vba - VLOOKUP by Combining 2 Columns to Form a Unique Key

Question

For the following 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.

Answer 1

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