Vlook Up Formula: Based on Two Criteria

[adamuce]

Hello, good people of this forum, I was wondering if someone could kindly help.

Cells A2:B6 is my data. Based on the yellow shaded area I want the corresponding result in C2:C6 shaded in blue.

So for example ,
Criteria 1 is USA and Criteria 2 is Mexico I want the result SS which we can see from the yellow shaded area.

I think the formula to use is Vlookup??? Could someone provide me the formula please



I have provided the sheet in excel too

PS: I have only posted this here and not any other forum. I remember :D

 

[Chihiro]

in C3:
=INDEX($C$10:$C$21,MATCH(A3&B3,$A$10:$A$21&$B$10:$B$21,0))

Confirmed as array (CTRL + SHIFT + ENTER)

Or using sumproduct.
=INDEX($C$10:$C$21,SUMPRODUCT(($A$10:$A$21=A3)*($B$10:$B$21=B3)*(ROW($C$10:$C$21)-9)))

 

[adamuce]

in C3:
=INDEX($C$10:$C$21,MATCH(A3&B3,$A$10:$A$21&$B$10:$B$21,0))

Confirmed as array (CTRL + SHIFT + ENTER)

Or using sumproduct.
=INDEX($C$10:$C$21,SUMPRODUCT(($A$10:$A$21=A3)*($B$10:$B$21=B3)*(ROW($C$10:$C$21)-9)))

Hi Chihiro! You really are as fast as a Ninja. Many Many Thanks!

 

[shrivallabha]

LOOKUP can also be used (non CSE). Probably will be faster on larger dataset!

=LOOKUP(2,1/(($A$10:$A$21=A3)*($B$10:$B$21=B3)),$C$10:$C$21)

 

[bosco_yip]

LOOKUP can also be used (non CSE). Probably will be faster on larger dataset!

=LOOKUP(2,1/(($A$10:$A$21=A3)*($B$10:$B$21=B3)),$C$10:$C$21)

Hi shrivallabha,

1] Aladin Akyurek of MrExcel.com had commented regarding speed of 2 criteria Lookup formulas in the order of :

1) DGET
2) INDEX/MATCH, supported with a concat column (helper column).
3) INDEX/MATCH with conditionals; this requires CSE.
4) LOOKUP with a division conditional.

2] Aladin in his post had advised the formula of
LOOKUP(2,1/conditionals, result range) --> running speed in slow
because of this part : 1/conditionals
-Lookup will make calculation every time in the Lookup criteria during the searching instead of the normal direct searching.

3] Only in small scale of data, LOOKUP(2,1/….) can be recommended in use due to the benefit of simple structure and non-CSE.

4] You may use a VBA timer to test the formulas speed in
confirmation.

Regards
Bosco

 

[shrivallabha]

Hi shrivallabha,

1] Aladin Akyurek of MrExcel.com had commented regarding speed of 2 criteria Lookup formulas in the order of :

1) DGET
2) INDEX/MATCH, supported with a concat column (helper column).
3) INDEX/MATCH with conditionals; this requires CSE.
4) LOOKUP with a division conditional.

2] Aladin in his post had advised the formula of LOOKUP(2,1/conditionals, result range) running speed in slow
because of this part : 1/conditionals
-Lookup will make calculation every time in the Lookup criteria during the searching instead of the normal direct searching.

3] Only in small scale of data, LOOKUP(2,1/….) can be recommended in use due to the benefit of simple structure and non-CSE.

4] You may use a VBA timer to test the formulas speed in
confirmation.

Regards
Bosco

Thank you Bosco for the pointer. My apologies for giving statement based on assumption (and not testing it). It was based on premise that LOOKUP uses binary search which tends to be faster.

I have done testing using setup using items 2,3,4. I gave skip to DGET as it doesn't seem to like copying down on its own. Attached is the test file using above data. Results are as below (bold portions indicate number of rows):

995328
-Index Match Array 2.710938
-Lookup Non Array 3.269531
-Index Match Helper Col Normal 1.710938

497664
-Index Match Array 1.382813
-Lookup Non Array 1.484375
-Index Match Helper Col Normal 0.839844

248832
-Index Match Array 0.683594
-Lookup Non Array 0.8125
-Index Match Helper Col Normal 0.453125

124416
-Index Match Array 0.285156
-Lookup Non Array 0.367188
-Index Match Helper Col Normal 0.199219

62208
-Index Match Array 0.144531
-Lookup Non Array 0.179688
-Index Match Helper Col Normal 0.089844

Speed wise results are precisely as Aladin has predicted. However, there's no huge difference between INDEX/MATCH array and LOOKUP. So I'd rather use LOOKUP.

But the helper columns approach is significantly faster than above two approaches and it is simple. Simple still is beautiful