If match, return value associated with match

[jenny_journalist]

Using Excel 2003

I'm trying to figure out an appropriate formula for the following problem:

I have sheet1, on which I have columns labeled ID, Last_Name and First_Name.

I have sheet2, on which I also have columns labeled ID, Last_Name and First_Name.

On sheet1, the last names and first names match properly, but the IDs have become scrambled.

on sheet1 I want to my formula to return the correct ID number from sheet2.

For example:

Please Login or Register  to view this content.

John Smith's ID is, in fact NOT 1000. I need to search for his name on sheet2 and find the correct ID.

Please Login or Register  to view this content.

sheet2 is correct. I want Column D1 on sheet1 to search for a name match and then return the correct ID from Sheet2, Column A.

I can't just do a sort because:

1. There may be some names on one sheet that are not on the other, and I will have to look those up manually.
2. There is other data on sheet1 that is properly associated with the name columns and I mustn't get that scrambled.

I do know how to make another column on each that combines columns B&C to make it easier to search for exact matches, if I need to.

I think this needs some sort of =IF(logical_test, [value_if_true],[value_if_false]) formula, but it's more complicated than what my limited skills allowed me to figure out!

Thanks,

Jenny

[DonkeyOte]

I suspect you can use LOOKUP here

Sheet1!D1
=LOOKUP(2,1/((Sheet2!$B$1:$B$1000=$B1)*(Sheet2!$C$1:$C$1000=$C1)),Sheet2!$D$1:$D$1000)

[jenny_journalist]

That lookup formula confused me a lot, I have to admit! I played around with it some though and read more information about the LOOKUP function and I did figure out a way to make it work that I understood. Here is what I did:

On both sheet1 and sheet2:

Column D

Please Login or Register  to view this content.

(combine last and first names to make a unique record)

Then, sheet 1, Column E was for my correct ID:

Please Login or Register  to view this content.

(Look at cell D2. Find its match in the D column on sheet2. Return the value from Column A on sheet2 that is on the same row.)

It seems to have worked! Thank you for pointing me in the right direction.

Jenny

[jenny_journalist]

I spoke too soon and had to remove my "solved" prefix. :-(

I see now that some people now have the same ID listed in my new sheet.

I am asking it to look up a cell in which I have combined last and first names, making each cell unique. It is returning incorrect IDs instead of an error when it encounters a name that is not on the other sheet.

I read the rules for the lookup function, and I guess it is returning a value for the next lowest match.

Maybe I should go back to your original formula, but it is confusing to me and since the actual sheet names and columns don't match what I'm using, I'm lost.

urgh. I'm not giving up - just delayed a bit.

[Paul]

Hi Jenny,

Without using the combined name column (using the data layout in your original post), you should be able to use the following array formula in D1 (and filled downward) to match Sheet1 columns B&C with Sheet2 columns B&C and return the correct value from Sheet2 column A (hopefully that makes sense!):

=INDEX(Sheet2!A:A,MATCH(B1&C1,Sheet2!B:B&Sheet2!C:C,0))

After typing or editing this formula, you must press CTRL+SHIFT+ENTER, not just ENTER. When done properly Excel will automatically insert braces, { }, around your formula.

Hope that helps!

[jenny_journalist]

After much experimentation, I found something that sort of works, but I need to combine them all together.

My actual data is more complicated than the example I gave. I need to have one cell that checks against three different columns on sheet2 before returning an error.

If this returns a value, stop here:

Please Login or Register  to view this content.

If it doesn't, check this:

Please Login or Register  to view this content.

If that doesn't match, check this:

Please Login or Register  to view this content.

If none of those match, return #N/A

Can I combine them all into one big IF statement? I need to check each of them separately, rather than just across the columns.

I didn't want to go into great detail about the data, but if it will help I can explain more about it.

I didn't use the curly brackets since I'm not looking across more than one column at a time.

[Paul]

Hopefully this combined formula works for you, otherwise you'll really need to explain your data (preferably by uploading a copy of your workbook).

Please Login or Register  to view this content.

[DonkeyOte]

Another alternative would (based on my understanding) be:

Please Login or Register  to view this content.