vlookup with multiple partial matches

**richx** · 07-09-2019, 10:05 AM

hi all,

I'm wondering if it is possible to use vlookup for multiple partial matches. What I mean by that is say I have first name in column C, last name in column D (on sheet2). In column C of sheet3 of the same workbook, the cells have the first name and last name somewhere in the cell, but also a lot of other useless information. Can you use vlookup to search for the first name and corresponding last name for a cell in column C(sheet3), and then return the entirety of that cell and not just the first/last name. Also, the first and last name in cell C(sheet3) may be separated by other words, sometimes words may follow the last name, words may be present before the first name, or it could only be first name "space" last name. The only constant is that last name will always be after first name.

This is the formula I came up with, but its only matching the first name, and so returning the wrong entries.
=VLOOKUP(C2&D2&"*", Sheet3!$C$2:$C$220, 1, FALSE)

thanks

**ChemistB** · 07-09-2019, 10:15 AM

Try

Formula:

Please Login or Register  to view this content.

**richx** · 07-09-2019, 10:26 AM

thanks for the response, but its still only returning entries that match first name, but not last name. it seems like the formula just reads the C2 and * and returns first entry available, and doesn't even bother to check for D2.

is there a way to check if the returned entry has D2, and if not, then skip and search again, and repeat etc. until the formula finds a cell with first and last name?
I know theres the isnumber(find()) to see if D2 is in the returned entry, but I don't know how to skip and search again.
Thanks