Returning a value in one cell from values in two other cells
 

I have an address list in one worksheet with: Last Name, First name, Address,
City, phone No. These addresses are sort alphabetically by last name.
On another worksheet I am attempting to match data from the first sheet
using the VLOOKUP function.
PROBLEM:
If there are three people with the same last name (e.g. "Brown"), but they
have different first names (e.g. "Peter", "John", "Susan"), and they live at
three different addresses, the LOOKUP function only returns the First name,
Address etc. of the first person on the list (e.g. "John"). If I try to
expand the search for both last name and first name I get an error message.
Any help would be appreciated

Returning a value in one cell from values in two other cells
 

"Fello" wrote...
I have an address list in one worksheet with: Last Name, First name,
Address, City, phone No. These addresses are sort alphabetically by last
name. On another worksheet I am attempting to match data from the first
sheet using the VLOOKUP function.
PROBLEM:
If there are three people with the same last name (e.g. "Brown"), but
they have different first names (e.g. "Peter", "John", "Susan"), and
they live at three different addresses, the LOOKUP function only returns
the First name, Address etc. of the first person on the list (e.g.
"John"). If I try to expand the search for both last name and first name
I get an error message.

You get the error message because the first name isn't part of the Last Name
cells in the 1st column in the 1st table.

The easiest way to handle this involves using additional columns of
formulas. If Last Name, First Name, Address, etc. were in columns A through
E and began in row 2, then enter the following formula in cell F2.

F2:
=A2&"|"&B2&"|"&C2&"|"&D2

Fill F2 down as far as needed so there's a formula in column F for each
record in columns A-E. These formulas concatenate last name, first name,
address and city. Then if your second table contains the same fields in the
same relative positions, but, say, starting in cell G9, use formulas like

X9:
=MATCH(G9&"|"&H9&"|"&I9&"|"&J9,OtherSheet!$F$2:$F$ 1000,0)

and fill down for each row in the second table. These formulas will return
the row index within column F in the first table when there's a match
against the current record in the second table or #N/A if there's no match.

Returning a value in one cell from values in two other cells
 

"Harlan Grove" wrote:

"Fello" wrote...
I have an address list in one worksheet with: Last Name, First name,
Address, City, phone No. These addresses are sort alphabetically by last
name. On another worksheet I am attempting to match data from the first
sheet using the VLOOKUP function.
PROBLEM:
If there are three people with the same last name (e.g. "Brown"), but
they have different first names (e.g. "Peter", "John", "Susan"), and
they live at three different addresses, the LOOKUP function only returns
the First name, Address etc. of the first person on the list (e.g.
"John"). If I try to expand the search for both last name and first name
I get an error message.

You get the error message because the first name isn't part of the Last Name
cells in the 1st column in the 1st table.

The easiest way to handle this involves using additional columns of
formulas. If Last Name, First Name, Address, etc. were in columns A through
E and began in row 2, then enter the following formula in cell F2.

F2:
=A2&"|"&B2&"|"&C2&"|"&D2

Fill F2 down as far as needed so there's a formula in column F for each
record in columns A-E. These formulas concatenate last name, first name,
address and city. Then if your second table contains the same fields in the
same relative positions, but, say, starting in cell G9, use formulas like

X9:
=MATCH(G9&"|"&H9&"|"&I9&"|"&J9,OtherSheet!$F$2:$F$ 1000,0)

and fill down for each row in the second table. These formulas will return
the row index within column F in the first table when there's a match
against the current record in the second table or #N/A if there's no match.


Thanks for your help. Very instructive