Vlookup with 2 parameters and multiple results

[nmprodan]

I'd like to find the row in the 'Table' sheet that contain both B2 & B3(first and last name) in the 'Names' sheet. The result would return the contents of the matching J (10) column in the cells from B5 and down if there are more than 1 result.

The sheet is attached. I've tried many array formulas, but I'm having no luck.

Thanks for any help, it's much appreciated!

[Fotis1991]

Hi

Do you mean something like tis in B5?

=INDEX(Table1[10],MATCH(Names!B$2&Names!B$3,Table1[1]&Table1[3],0))

This is an Array formula.Confirm this, using Control+Shift+Enter. No just enter.

[nmprodan]

That is almost is, but the problem I'm having is the first and last names are not always in columns 1 & 3. Sometimes they're backward or even in different columns altogether. So the search is to find the rows that contain both first and last name anywhere in that row. So B2 & B3 would return 200818944 & 200819300 (rows 3 and 15).

[Fotis1991]

In your example with Peggy, is not difficult to get the result that you want since all the other columns are empty(Just add as many & as we need, in MATCH function).

But since some columns have other informations...i can not think something....Sorry.

[nmprodan]

Would this need to be a VBA script?

[Fotis1991]

Maybe. But i don't know anything about VBA.

Someone else??

[nmprodan]

Thanks for trying.

Anyone else have any ideas?