Double column VLOOKUP

[gyoung]

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]

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

 

[wi_guy]

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]

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