Index Match & small

[streetlyfe]

Hello All,

I am trying to return various results using one lookup value, I have this formula in place =INDEX(ISBN,MATCH(comparative!$B$1,Prod_No,0)) which is bring me back a single result, the prod_no has a series of isbn numbers attached to it, so I am think I need to incorporate a small formula into my formula to bring back the multiple ISBN's, and this is where I need help, I am not sure how to incorporate it into my existing formula.

Thanks in advance for your help.

 

[Marcelo Branco]

Welcome to Mr Excel

See if this example helps

Named ranges
ISBN --->A2:A11
Prod_No-->B2:B11

	A​	B​	C​	D​	E​	F​
1​	ISBN​	Prod​		Prod​	Count​	List​
2​	ISBN_1​	Prod1​		Prod1​	3​	ISBN_1​
3​	ISBN_2​	Prod2​				ISBN_3​
4​	ISBN_3​	Prod1​				ISBN_4​
5​	ISBN_4​	Prod1​
6​	ISBN_5​	Prod2​
7​	ISBN_6​	Prod3​
8​	ISBN_7​	Prod2​
9​	ISBN_8​	Prod4​
10​	ISBN_9​	Prod5​
11​	ISBN_10​	Prod3​

put in D2 the prod of interest

Formula in E2
=COUNTIF(Prod_No,D2)

Array formula in F2 copied down
=IF($E$2>=ROWS(F$2:F2),INDEX(ISBN,SMALL(IF(Prod_No=$D$2,ROW(ISBN)-MIN(ROW(ISBN))+1),ROWS(F$2:F2))),"")

confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[streetlyfe]

Hi Marcelo,
The formula is working but with a flaw, its returning duplicate values when I only need one instance, the first value comes from my index match formula, and its then duplicated by your if small array formula... can this be modified to just bring back one qualifying instance for example (below)... is there a way to modify the formula?

ISBN	9781405268028
	9781405268028
	9781780313733
	9781780313757
	9781780313740

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[Marcelo Branco]

Try this

	A​	B​	C​	D​	E​
1​	ISBN​	Prod​		Prod​	List​
2​	ISBN_1​	Prod1​		Prod1​	ISBN_1​
3​	ISBN_2​	Prod2​			ISBN_4​
4​	ISBN_1​	Prod1​			ISBN_3​
5​	ISBN_4​	Prod1​
6​	ISBN_3​	Prod1​
7​	ISBN_6​	Prod3​
8​	ISBN_7​	Prod2​
9​	ISBN_8​	Prod4​
10​	ISBN_9​	Prod5​
11​	ISBN_10​	Prod3​

Array formula in E2 copied down
=IFERROR(INDEX($A$2:$A$11,SMALL(IF($B$2:$B$11=$D$2,IF(ISNA(MATCH($A$2:$A$11,E$1:E1,0)),ROW($A$2:$A$11)-ROW($A$2)+1)),1)),"")

confirmed with Ctrl+Shift+Enter

M.

 

[streetlyfe]

Hi Marcelo,

Is it possible you could breakdown what this formula is actually doing particularly the MIN aspect of the formula, as the subsequent queries following on from the first one it returns aren't related to the prod no at all which is the info around which all the queries should pivot around, it also doesn't change.... is it possible that this aspect of the formula could be tweaked, as the formula is should only return ISBNs related to the prod_no

I am not familiar with the MIN formula when used with Rows so I am struggling to breakdown what its actually doing, but I assume its part of the subsequent results that fall off the back of the initial result so maybe the amendment needs to be in here.

Thanks again

Cheers...

 

[Marcelo Branco]

To see what the formula does, step by step, select the cell that houses the formula and try
Formulas > Evaluate formula

M.