INDEX and MATCH

[John Moore]

Hi guys, I am currently using the below formula that returns certain data
from a report ( multiple rows and columns ) and it's based on a product name.
What I would like to do is have the formula return data based on two things,
product name and product type but not sure it can be done with INDEX and
MATCH .... the data is returned based on B4 ( product name in that cell ) in
the below example ,,,, Commodities is my sheet tab name, I would like to have
the data returned based on B4 and D4 ( product name and product type ).

=IF(ISERROR(SMALL(Commodities!$L:$L,ROWS($B$4:B4))),"",
INDEX(Commodities!N:N,MATCH(SMALL(Commodities!$L:$L,
ROWS($B$4:B4)),Commodities!$L:$L,0)))

 

[smartin]

Hi guys, I am currently using the below formula that returns certain data
from a report ( multiple rows and columns ) and it's based on a product name.
What I would like to do is have the formula return data based on two things,
product name and product type but not sure it can be done with INDEX and
MATCH .... the data is returned based on B4 ( product name in that cell ) in
the below example ,,,, Commodities is my sheet tab name, I would like to have
the data returned based on B4 and D4 ( product name and product type ).

=IF(ISERROR(SMALL(Commodities!$L:$L,ROWS($B$4:B4))),"",
INDEX(Commodities!N:N,MATCH(SMALL(Commodities!$L:$L,
ROWS($B$4:B4)),Commodities!$L:$L,0)))

Hi John,

For my money I prefer SUMPRODUCT for multiple condition lookups. This
site gives an outstanding explanation of the technique:

http://xldynamic.com/source/xld.SUMPRODUCT.html

If you want to see an INDEX/MATCH solution check this site. It also
shows a method using SUMPRODUCT:

http://blog.livedoor.jp/andrewe/archives/7336895.html

Let us know if you get stuck.