Need a formula to make Excel search for info

[Memzzie]

Please help! This would save me HOURS AND HOURS of work!

I need a formula to make Excel search columns C-E (all rows) of worksheet 1 for data contained in columns A-C of worksheet 2. If matching data is found, I want it to insert the data from column J worksheet 2 (same row as A-C) into column G of worksheet 1. I need to be able to apply the formula to each row, columns will remain the same.

Is this possible? I hope it makes sense, this is WAY beyond my Excel knowledge!

See attached for the worksheets.

Thanks in advance!

Melissa

[arlu1201]

First of all, please change your heading to something that explains your problem.
2nd - Please attach a sample workbook.

[JBeaucaire]

Hi Memzie, welcome to the forum.

You do need to not be so frantic in your posts, ok? You'll surely get help here fast and easy if simply ask your questions, that's why we're here.

First, EDIT your post #1 above, click GO ADVANCED and correct your thread title. As per Forum Rules, you must give your thread an accurate title. (Read the rules by clicking the link above in the menu bar.)


Secondly, it's simplest to show us what you mean. Click GO ADVANCED in the Quick Reply box below and use the paperclip icon to post up a copy of your workbook. In the sample workbook manually mockup some desired results so we can see what you're after.

[GeneralDisarray]

you want to do a gigantic FindNReplace right? test this out. To run the macro, press alt+F8 and select & run FNR_Test.

[Whizbang]

Please review the attached workbook.

This formula goes into Worksheet 1, G2

=INDEX('Worksheet 2'!A:J,MATCH('Worksheet 1'!C2&'Worksheet 1'!D2&'Worksheet 1'!E2,'Worksheet 2'!A:A&'Worksheet 2'!B:B&'Worksheet 2'!C:C,0),10)

Note that this is an array formula (do a Google search for what that means). This formula must be confirmed by CTRL+SHIFT+ENTER, rather than just ENTER.

Then just copy on down and you are good to go.

[JBeaucaire]

Whizbang, you can't do whole-column array formulas in xl2003 without getting #NUM errors. At least I've never been able to and your sheet does it for me to if I try to reconfirm your formulas as is.

I'm a little more low-tech. Add a column to your ws2 to create a unique "key" from the 3 elements you want to match. Then use that key in a "match" function on ws1. See attached.

[Whizbang]

You're right, of course. I had forgot about that.

=INDEX('Worksheet 2'!A1:J65536,MATCH('Worksheet 1'!C2&'Worksheet 1'!D2&'Worksheet 1'!E2,'Worksheet 2'!A1:A65536&'Worksheet 2'!B1:B65536&'Worksheet 2'!C1:C65536,0),10)

Memzzie,
JBeaucaire's solution is the best if you have many hundreds or thousands of rows. Array formulas are nice because the don't need helper columns, but they can slow down your worsheet if overused.

[Memzzie]

Thank you for your replies everyone, it's looking like it's going to work. My only problem now is that I had to send you a sample spreadsheet of course, not my actual spreadsheet. I thought I had sent a close enough sample, but my original has 60 worksheets and are not named Worksheet 1, 2 and so on. I thought I could make the alterations neccesary to match name for name and cell for cell, but it's just not working.

Let me give you the exact names of 2 of the worksheets and the cell numbers again, maybe I'm screwing it up somehow in my explanation.

"Master" is the name of Worksheet 1, "Aiken CSD" is the name of Worksheet 2.

I need to search columns C, D, E of Master for the information contained in columns A, B, C of Aiken CSD. If there is a match, I need to fill column G of Master (in whatever row the match was found in) with the info in column J of Aiken CSD. If there is no match found, it needs to remain blank. I have thousands of rows. I didn't know that mattered until reading some of your responses.

Thanks!

[JBeaucaire]

I need to search columns C, D, E of Master for the information contained in columns A, B, C of Aiken CSD. If there is a match, I need to fill column G of Master (in whatever row the match was found in) with the info in column J of Aiken CSD.

I'm sorry, your quote above seems to be an exact replica of the original problem. In the sample workbook I gave you, change the names of the two sheets to MASTER and AIKEN CSD and that will update your formulas for them, then enter them into your real workbook exactly as you see them.

If there are MORE sheets to deal with, this formula is currently only dealing with ws2 (Aiken CSD), If you need this formula to check other sheets, too, we'll need to see a better example, with perhaps 3-4 sheets.

[Whizbang]

Here is some reading material on the Index and Match functions:

Single criteria:
http://www.mrexcel.com/articles/exce...ndex-match.php
http://www.ozgrid.com/Excel/left-lookup.htm

Multiple criteria:
http://support.microsoft.com/kb/214142

[Memzzie]

THANK YOU SO MUCH everyone, this worked! You have saved me so much work!

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.