lookup field using two columns
 

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.

lookup field using two columns
 

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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Fran" wrote in message
...
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.

lookup field using two columns
 

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.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" wrote in message
...
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.
--
HTH,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-
"Fran" wrote in message
...
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.

lookup field using two columns
 

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

"Fran" wrote:

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.

lookup field using two columns
 

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.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Fran" wrote in message
...
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.....

"Fran" wrote:

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.