3 way vlookup with search formula

[amanp786]

hello,

I have a spreadsheet with 2 database and I want to use 3 way or 2 way vlookup up to get the missing column in database 2. This is a example but my actual data is 25K rows. I have attached the spreadsheet with an example. Any help will be great. Thank You

[FDibbins]

While I am looking at your file, Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version.

[Moo the Dog]

You could try this ARRAY formula in cell M3 and fill it down:

=INDEX($E$3:$E$7,MATCH(1,(ISNUMBER(FIND(LEFT(I3,FIND(",",I3)-1),$A$3:$A$7))*($D$3:$D$7=L3)),0))

Remember, ARRAY formulas are applied using CTRL + SHIFT + ENTER, not just ENTER!

Also Mr. Brown's name is misspelled on one of the lists... not going to find matches with typos. I changed it to "Brown" and it looks like all of the correct matches were made.

- Moo

[amanp786]

Moo - Awesome! I understand what you did Moo. Would you call this a 3 way vlookup? Just for learning purposes, what if the Left 10 column (D & L) were not there, how would this formula change. Thanks once again.

FDibbins - Sorry about that I have updated my profile. Thanks.

[Moo the Dog]

If the 'Left 10' column isn't used, getting the correct data is going to be more difficult, since you would just be matching last names... which could lead to mistaken duplication. But the formula would be a simpler non-array INDEX/MATCH formula:

=INDEX($E$3:$E$7,MATCH("*"&LEFT(I3,FIND(",",I3)-1)&"*",$A$3:$A$7,0))

At least it seems to work for this small list...

- Moo

[amanp786]

Moo- that was the problem I ran into at the beginning when I did Index/Match but if you were to include other factors for example let's say they date, than this becomes a 3 way vlookup and I guess you would go back to your original formula. What I am trying to learn is if I can use the last name from Database 2 and the left 10 of address and date to find the ID from database 1. You have been very very helpful sir. Thanks a bunch