Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing related cell values and returning total to summary | Excel Discussion (Misc queries) | |||
lookup returning incorrect cell values | Excel Worksheet Functions | |||
Returning Values | Excel Discussion (Misc queries) | |||
Returning a sum when looking up two different values | Excel Worksheet Functions | |||
Returning all values | Excel Discussion (Misc queries) |