Menu
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.
599
By default, 'VLOOKUP' cannot help with a two-criteria (or more) lookup. It is customary to change to the more flexible combination of functions, 'INDEX' and 'MATCH' and turning the formula into an array formula. However, this is not as easy and intuitive as using a simple 'VLOOKUP'.
The VLOOKUP function can be combined with other functions such as the Sum, Max, or Average to calculate values in multiple columns. As this is an array formula, to make it work we simply need to press CTRL+SHIFT+ENTER at the end of the formula.
To find the accurate result for multiple columns using VLOOKUP you will need to make some modifications to the formula. The Concatenate Operator (“&”) helps to use VLOOKUP on multiple columns to satisfy multiple conditions.
Then you can vlookup using the concatenated key. Show activity on this post. 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.
The syntax for VLOOKUP is =VLOOKUP (value, table_array, col_index, [range_lookup]). In its general format, you can use it to look up on one column at a time. However, tweaking the formula allows us to use VLOOKUP to look across multiple columns.
For a same date, you have different quotation for each currency So, the only way to return the correct value, it's to build an ID with the combination of the column Date and Currency. VLOOKUP has been designed (in 1983) to search on the first column of your range of data.
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
92
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
