Match, Index, Vlookup Combinations

Topic

New Reply

I need to lookup data based on two separate criteria. I made a stab at it with the match and vlookup functions. With MATCH I can get the start of the lookup area but not the end.I sure there’s a much more elegant way than how I’ve approached this, so any help would be appreciated.

Thanks

Reply | Quote

Replies

You could use the following formula in J5:

=SUMPRODUCT(($A$2:$A$40=$I5)*($B$2:$B$40=$J$2)*$C$2:$C$40)

and fill down. The formula for K5 would be

=SUMPRODUCT(($A$2:$A$40=$I5)*($B$2:$B$40=$J$2)*$D$2:$D$40)

If there is no match, the formula will return 0, so you should hide zeros.

Reply | Quote

Thanks! I came up with this also: =VLOOKUP($I5,INDIRECT(“a”&MATCH($J$2,$B$1:$B$40,0)&”:d”&MATCH($J$2,$B$1:$B$40,1)),COLUMN($C:$C))

But your way is more flexible and easier to work with.

Reply | Quote