Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
INDEX and MATCH
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))) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
INDEX and MATCH
John Moore wrote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Exact Match using INDEX, MATCH | Excel Worksheet Functions | |||
index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
index,match,match on un-sorted data | Excel Worksheet Functions |