Vlookup using 2 criteria

[ExtraExcelly]

I have two excel worksheets, both with Names entered in 2 columns: Last Name and First Name. On the second worksheet, I want to look up the name on the first worksheet and automatically populate one of the other columns.

The difficulty comes in that my first worksheet has so many names, it's likely that more than one person will have the same last name (that's how it's sorted). Is there anyway for VLOOKUP to lookup 2 columns and then populate with the third? See picture below, that might explain better. Thank you!

[NBVC]

Yes it is possible with either Index/Match or Sumproduct, based on what you want returned, text or number,

but no picture below....

[ExtraExcelly]

Sorry, here is the picture:

\1

I don't know how to make it show up in here.

[NBVC]

Using your sample, try:

=INDEX($C$2:$C$10,MATCH(1,INDEX(($A$2:$A$10=A11)*($B$2:$B$10=B11),0),0))

adjust ranges/references to suit.

[ExtraExcelly]

Thanks for your quick response. However, I got a "#VALUE" error when I input your formula into my sample. I even adjusted the arrays and it still didn't work. Do you have any other suggestions?

[NBVC]

Try again...copying the formula above.

You may have copied the formula too quickly as I had made a minor change....

[ExtraExcelly]

That's what it was, it works now in my sample! Now I'm trying to integrate it into my actual file and adjusting the references, but I can't figure out what the "1" means as the first argument for MATCH. I'm setting it up identical (or so I thought) to the sample and I'm getting a "#NUM" error.

[NBVC]

The 1 is the lookup value here...

the two conditions, ($A$2:$A$10=A11) and ($B$2:$B$10=B11) create an array of TRUEs and FALSEs depending on if the separate matches are found...

multiplying the 2 together, like so, ($A$2:$A$10=A11)*($B$2:$B$10=B11),

will coerce the TRUEs/FALSEs to 1s and 0s, respectively... and at the point where both arrays have a 1, is the point where a match of the 2 criteria is found in the same row... the internal INDEX() functions, re-creates the array for the Match() function to look up against... so Match(1,....) looks for the postion of that 1 where both criteria are met together .. and returns the position number which is then indexed on the outside INDEX() array...

so, you shouldn't get #NUM error if you entered the ranges and reference correctly. At most you would get #N/A if a match is not found.