CODEWITHSUNDEEP
excelexcel-formula
Bunny
Bunny

Reputation: 147

How to lookup value with multiple criteria in excel 2007 and newer

I would like to lookup for a value in excel table with multiple criteria. I have tried this:

=INDEX($D$2:$D$10,MATCH(1,($B$2:$B$10=A13)*($C$2:$C$10=A13),0))

but it doesn't work.

I use this one:

=LOOKUP(2,1/(Reservation!$A:$A=$A20)/(Reservation!$B:$B=F$5)/(Reservation!$D:$D=0),Reservation!$C:$C)

... which works fine, but it's extremely slow.

Is there anyway to use index with multiple criteria?

Upvotes: 3

Views: 208

Answers (1)

chancea
chancea

Reputation: 5968

Normally with a single criteria all you would have to do is type:

INDEX($D$2:$D$10,MATCH(A13,$B$2:$B$10,0))

And hit enter and you are done.

However, since you are using arrays inside your MATCH formula i.e. ($B$2:$B$10=A13) and ($C$2:$C$10=A13) you need to enter your formula using:

ctrl + shift + enter

If you want further reading you can see Microsoft's Guidelines and examples of array formulas

Upvotes: 3

Related Questions