lookup field using two columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
 
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.
 
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.....
 
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.
 
Back
Top