Double column VLOOKUP

gyoung · Mar 26th, 2002, 11:34 AM

I need to do a function like VLOOKUP but two colums of data needt to match, then return
the data from the third column.

I have a look up table in this format:
Last Name|First Name| Org|

The spreadsheet being loaded with "ORG" info
has the same format, but there are multiple
entries of last & first names.

I cannot use the standard VLOOKUP because I
have people with the same last names, or the
same first names. ie: I need to match both
before the "ORG" is laoded to the spreadsheet.

Any help would be appreciated.

Tim Francis-Wright · Mar 26th, 2002, 11:53 AM

Check out this web page: http://www.mvps.org/skp/xl00002.htm
that uses the MATCh function to do this
elegantly.

wi_guy · Mar 26th, 2002, 12:42 PM

The way I've gotten aroound this in the past is creating another column that concatinates the Lname and Fname, and then looks up that new column. If Lname and Fname were in Columns B and C, the formula (in Column A) would be +B1&C1 .....OR you could add a Comma Separator with +b1&", "&C1
Hope this isn't overly simplistic for you.

Aladin Akyurek · Mar 26th, 2002, 01:30 PM

Quote:

On 2002-03-26 10:34, gyoung wrote:
I need to do a function like VLOOKUP but two colums of data needt to match, then return
the data from the third column.

I have a look up table in this format:
Last Name|First Name| Org|

The spreadsheet being loaded with "ORG" info
has the same format, but there are multiple
entries of last & first names.

I cannot use the standard VLOOKUP because I
have people with the same last names, or the
same first names. ie: I need to match both
before the "ORG" is laoded to the spreadsheet.

Any help would be appreciated.

Two possible solutions:

1. Create a 4th column in the sheet where everything is. Supposing that A2:C100 houses the data of interest in sheet Data:

In D2 enter and copy down as far as needed:

=A2&"-@-"&B2

where A2 holds a last name and B2 a first name.

In the sheet,called Main say, where you want to retrieve the associated ORG values from Data, use the following formula:

=IF(COUNTIF(Data!$D$2:$D$100,A2&"-@-"&B2),INDEX(Data!$C$2:$C$100,MATCH(A2&"-@-"&B2,Data!$D$2:$D$100,0)),"Not found")

2.

In Main just use:

=IF(SUMPRODUCT(ISNUMBER(MATCH(A2&"-@-"&B2,Data!$A$2:$A$100&"-@-"&$Data!B$2:$B$100,0))+0),INDEX(Data!$C$2:$C$100,SUMPRODUCT(MATCH(A2&"-@-"&B2,Data!$A$2:$A$100&"-@-"&$Data!B$2:$B$100,0))),"Not Found")

Note. Method 1 trades off space against time, therefore faster. Method 2 will incur some performance costs (that's, it will cause recalculation at opening and closing of your WB -- time costs).

Mar 26th, 2002, 11:53 AM	#2
Tim Francis-Wright Board Regular Join Date: Feb 2002 Posts: 74	Check out this web page: http://www.mvps.org/skp/xl00002.htm that uses the MATCh function to do this elegantly. __________________ "Interfere? Of course we should interfere! Always do what you're best at, that's what I say!" -- The Doctor, Nightmare of Eden

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 26th, 2002, 11:34 AM	#1
gyoung New Member Join Date: Mar 2002 Posts: 1	I need to do a function like VLOOKUP but two colums of data needt to match, then return the data from the third column. I have a look up table in this format: Last Name\|First Name\| Org\| The spreadsheet being loaded with "ORG" info has the same format, but there are multiple entries of last & first names. I cannot use the standard VLOOKUP because I have people with the same last names, or the same first names. ie: I need to match both before the "ORG" is laoded to the spreadsheet. Any help would be appreciated.

Mar 26th, 2002, 12:42 PM	#3
wi_guy Board Regular Join Date: Mar 2002 Posts: 50	The way I've gotten aroound this in the past is creating another column that concatinates the Lname and Fname, and then looks up that new column. If Lname and Fname were in Columns B and C, the formula (in Column A) would be +B1&C1 .....OR you could add a Comma Separator with +b1&", "&C1 Hope this isn't overly simplistic for you.