CODEWITHSUNDEEP
excelexcel-formula
Cutter
Cutter

Reputation: 1820

VLOOKUP with two criteria?

Is there a formula that returns a value from the first line matching two or more criteria? For example, "return column C from the first line where column A = x AND column B = y". I'd like to do it without concatenating column A and column B.

Thanks.

Upvotes: 7

Views: 32440

Answers (6)

Colm Bhandal
Colm Bhandal

Reputation: 3831

Here is an answer that shows how to do this using SUMPRODUCT and table header lookups. The main advantage to this: it works with any value, numeric or otherwise.

So let's say we have headers H1, H2 and H3 on some table called MyTable. And let's say we are entering this into row 1, possibly on another sheet. And we want to match H1, H2 to x, y on that sheet, respectively, while returning the matching value in H3. Then the formula would be as follows:

=INDEX(MyTable[H3], ROUND(SUMPRODUCT(MATCH(TRUE, (MyTable[H1] & MyTable[H2]) = ($x1 & $y1),0)),0),1)

What does it do? The sum-product ensures everything is treated as arrays. So you can contatenate entire table columns together to make an array of concatenated valued, dynamically calculated. And then you can compare these to the existing values in x and y- somehow magically you can compare the concatenated array from the table to the individual concatenation of x & y. Which gives you an array of true false values. Matching that to true yields the first match of the lookup. And then all we need to do is go back and index that in the original table.

Notes

The rounding is just in there to make sure the Index function gets back an integer. I got #N/A values until I rounded.

It might be more instructive to run this through the evaluator to see what's going on...

This can easily be modified to work with a non table - just replace the table references with raw ranges. The tables are clearer though, so use them if possible. I found the original source for this here: http://dailydoseofexcel.com/archives/2009/04/21/vlookup-on-two-columns/. But there was a bug with rouding values to INTs so I fixed that.

Upvotes: 0

Raj Naveen
Raj Naveen

Reputation: 1

Actually, I think what he is asking is typical multiple results display option in excel. It can be done using Small, and row function in arrays.

This display all the results that matches the different criteria

Upvotes: 0

JimmyPena
JimmyPena

Reputation: 8754

I use INDEX/MATCH for this. Ex:

I have a table of data and want to return the value in column C where the value in column A is "c" and the value in column B is "h".

sample layout

I would use the following array formula:

=INDEX($C$1:$C$5,MATCH(1,(($A$1:$A$5="c")*($B$1:$B$5="h")),0))

Commit the formula by pressing Ctrl+Shift+Enter

After entering the formula, you can use Excel's formula auditing tools to step through the evaluation to see how it calculates.

Upvotes: 5

adayzdone
adayzdone

Reputation: 11238

True = 1, False = 0

D1 returns 0 because 0 * 1 * 8 = 0

D2 returns 9 because 1 * 1 * 9= 9

enter image description here

This should let you change the criteria: enter image description here

Upvotes: 7

andy holaday
andy holaday

Reputation: 2302

SUMPRODUCT definitely has value when the sum over multiple criteria matches is needed. But the way I read your question, you want something like VLOOKUP that returns the first match. Try this:

Multiple Criteria INDEX MATCH

For your convenience the formula in G2 is as follows -- requires array entry (Ctrl+Shift+Enter)

[edit: I updated the formula here but not in the screen shot] =INDEX($C$1:$C$6,MATCH(E2&"|"&F2,$A$1:$A$6&"|"&$B$1:$B$6,0))

Two things to note:

  1. SUMPRODUCT won't work if the result type is not numeric
  2. SUMPRODUCT will return the SUM of results matching the criteria, not the first match (as VLOOKUP does)

Upvotes: 3

Sjoerd
Sjoerd

Reputation: 75568

Apparently you can use the SUMPRODUCT function.

Upvotes: 0

Related Questions