Kamran Mumtaz askedIs there any way to use VLOOKUP for multiple criteria and I do not want to use CSE?;

My answer: No, not to my knowledge

Kamran Mumtaz:  This is the formula given by Aladin Akyurek without (CSE)...

=INDEX(Sheet3!$B$2:$B$65, MATCH(1,INDEX((Sheet3!$C$2:$C$65=E$1)*(Sheet3!$A$2:$A$65=$A3), 0, 1), 0))

----------------------------------------------------------------------------------------------------------------------------

This formula is really interesting, I have never seen this approach before. It definitely looks like an array formula but it is not. It opens up possibilities that I have not thought about before.

Example 1,

no more array formulas

This formula derives from one of the most popular blog posts here: Extract a unique distinct list

The original array formula:

=INDEX($A$2:$A$5,MATCH(0,COUNTIF($B$1:B1,$A$2:$A$5),0))

The new array formula in cell B2:

=INDEX($A$2:$A$5,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$5),0,0),0))

The new formula is NOT an array formula! Amazing! I am not sure if all array formulas can be converted to regular formulas but obviously some can.

Example 2,

no more array formulas example 2

This formula derives from one of the most popular blog posts here: How to return multiple values using vlookup

Original array formula:

=INDEX($B$2:$B$5, SMALL(IF($E$1=$A$2:$A$5, MATCH(ROW($B$2:$B$5),ROW($B$2:$B$5)), ""), ROW(A1)))

New array formula in cell E2:

=INDEX($B$2:$B$5, SMALL(INDEX(($A$2:$A$5=$E$1)*(MATCH(ROW($B$2:$B$5), ROW($B$2:$B$5)))+($A$2:$A$5<>$E$1)*1048577, 0, 0), ROW(A1)))

Download example file

no arrays.xlsx

What would you prefer? Array formulas or somewhat longer and more complicated regular formulas?