CODEWITHSUNDEEP
vbaexcel
jmaz
jmaz

Reputation: 527

VLOOKUP by Combining 2 Columns to Form a Unique Key

For the following table,

excel table

I want to look up the value in col C. Since the values in col A and col B are not unique, VLOOKUP fails. For example, VLOOKUP(1,table,3) returns 5 and never 1.

However, the combinations of cols A & B are unique. For example, 1blah = 5, while 1foo = 1.

How can I use the combination of cols A & B as a unique key to return the corresponding value in col C?

I'm not sure if this should be implemented with worksheet functions or a custom VBA function. I tried using CONCATENATE to generate the unique key, but this did not work because this results in combinations of numbers and strings.

Upvotes: 9

Views: 26126

Answers (4)

Marcus Gröber
Marcus Gröber

Reputation: 51

Expanding on the accepted answer, it is also possible to use column and table names in this type of formula if you are using named tables, which makes the whole thing somewhat easier to read:

Screenshot of two Excel tables, one corresponding to the original example, and one showing how to use a lookup from a table column.

Assuming the first table from the example is called MyTable, with columns named "First", "Second", "Third", you can look up its contents based on a compound key from the first and second columns of another table (named "One" and "Two") with a formula like this in column "Three":

=INDEX(MyTable[Third], MATCH([@One]&[@Two], MyTable[First]&MyTable[Second], 0))

Using "0" for the last parameter of MATCH allows MyTable to be unsorted and returns #NV if no match is found.

It is usually not necessary to remember the exact syntax, as Excel will auto-complete the column and table names if you highlight the corresponding cells in your worksheet while writing the formula.

Upvotes: 0

AgentGG
AgentGG

Reputation: 1

You can use VLOOKUP if your lookup value is a concatenation of your two or more key fields (A2&B2). Then, add a first sorted column in your LUT sheet with the array that is the concatenation of the same key fields. Viola.

Upvotes: 0

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

You can use an array formula:

=INDEX($C$1:$C$7,MATCH("1foo",$A$1:$A$7 & $B$1:$B$7,0))

just select in example D1, enter formula in formula bar and press CTRL+SHIFT+ENTER to evaluate it

Upvotes: 10

Tmdean
Tmdean

Reputation: 9299

The way I usually do it is by concatenating the values separated by a pipe character (|). See the formula in the screenshot below.

=A1&"|"&B1

Then you can vlookup using the concatenated key.

=VLOOKUP("1|foo",$C$1:$D$7,2,FALSE)

Upvotes: 2

Related Questions