lookup field using two columns

[Guest]

Simple problem - hard to figure. (This report is imported with information
missing - it only contains a first initial instead of the name - AND the
initial could be wrong - so I consider the column empty) Column A has last
name, Column B has first initial, Column F has birthdate.

I am trying to match the Last Name (Column A) and Birthdate (Column F) to
give me the First name......(Column B)

I created a 'lookup file" on sheet 2 showing last names AND first names and
Birthdate. I know that VLookup with give me last name and show the First
name... but there are hundreds of names. I need to Match 2 criterial - the
last name with the Birthdate to give me the first name. Would really
appreciate help. Working on this for tomorrow at work.

 

[Ragdyer]

Sheet1 has your imported data.
Row1 is headers.
Column A has last name.
Column F has birthdate.

Sheet 2, Row1 is headers and has last name in A, first name in B, birthdate
in C.

Enter this *array* formula in B2 of Sheet1:

=INDEX(Sheet2!$B$2:$B$200,MATCH(1,(Sheet2!$A$2:$A$200=A2)*(Sheet2!$C$2:$C$20
0=F2),0))

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

Assuming datalist is 200 rows, *after* CSE entry, drag down to copy to B200.

 

[Ragdyer]

OR, this *NON* array wrinkle:

=INDEX(Sheet2!$B$2:$B$200,MATCH(A2&F2,INDEX(Sheet2!$A$2:$A$200&Sheet2!$C$2:$
C$200,0),0))

However, the *caveat* with this type of formula is that you lose the
integrity of the *individual* cell contents, which become merged with the
other referenced cell(s).

For instance, ABC and DEF will register as a match with AB and CDEF.

In this case however, since we have numbers in one column and text in the
other, it's a relatively safe assumption that such a "merged" match will not
exist.

 

[Guest]

Thank you so much..... it did work. Funny - I teach Excel and am unfamiliar
with the way this worked. Where did you get info on this - or do you do some
programming? Seems that it's fairly common to look up something that has to
have 2 fields matching.... surprised there isn't an "advanced lookup" in the
tools.....

 

[Ragdyer]

MOST of my XL knowledge has been garnered from these NGs.

Spend a little time here each day, and you'll be surprised at all that
you'll pick up ... actual knowledge as well as big and little tricks.

Thanks for the feed-back.