Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 925
Cutter Avatar asked May 14 '12 12:05

Cutter


2 Answers

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

like image 119
adayzdone Avatar answered Nov 15 '22 07:11

adayzdone


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.

like image 30
JimmyPena Avatar answered Nov 15 '22 09:11

JimmyPena