multiple match results

[adsako]

Hello All

I'm trying to find list of values related to one criteria ... I have used INDEX in combination with SMALL function (I work on Excel 2017 version hence AGGREGATE function is not available) however i get in return #DIV/0!

formula I applied
{=INDEX($A$2:$A$50, SMALL(($B$2:$B$50=$D$1)/($B$2:$B$50=$D$1)*(ROW($B$2:$B$50)-ROW($B$1)), ROW($C$2:C2)))}

for instance for value CH2307-0046 I should have received 42472 and 42473 (as those are in row 2 and 6 however i get #DIV/0!

not sure where is the problem

can you please advise

attached file

thanks

[tim201110]

{=INDEX($A$2:$A$50, SMALL(if($B$2:$B$50=$D$1)*($B$2:$B$50=$D$1),ROW($B$2:$B$50)-ROW($B$1)), ROW($C$2:C2)))}

[adsako]

Hi
thanks for this

I'm still trying to understand why my formula is not working?
when I'm going through entire evaluation all seems to be OK bur I get error in return

Maybe im stupid but i don't know where is the problem in my formula

thanks

[adsako]

Hi again

i applied your solution however I get a message that formula contains an error

cannot find it to be honest :/

thanks

[tim201110]

did you try to evaluete it?

[adsako]

Hi
unfortunately formula you have suggested seems not working ... when I applied your solution I did not get list of values but only one value from line 6 (and first value related to criteria is in line 5)

attached file where your solution was applied ...

thanks

[adsako]

Hi, yes ... returns error

[tim201110]

=index($a$2:$a$50, small(if(($b$2:$b$50=$f$1)*($b$2:$b$50=$f$1),row($b$2:$b$50)-row($b$1)), rows($e$2:$e2)))

[adsako]

Hi
I don't know why but formula works partially ...

for instance
for S130 - 3/4" DIA (NON-MAG) i should get 7 results but formula returns only 3

I'm confused ....

where is an error

[popipipo]

Please Login or Register  to view this content.

Look for the differences

[adsako]

Hello all

I'm reopening this topic ...

I need to get list of unique values (excluding duplicates) from 'column A' matching criteria in cell H1 (cell H1 is with drop down list)
File is excel official table which is connected to server and is refreshing automatically

in column H I applied the following formula:

=INDEX(Table1[[#All],[WO]], SMALL(IF((Table1[[#All],[P/N]]=$H$1)*($H$1=Table1[[#All],[P/N]]), ROW(Table1[[#All],[P/N]])-ROW($C$1)), ROWS($G$2:G2)))

however it does not return unique values (does not exclude duplicates)
for instance for ASK1671_CH2307-0123 I should get only 2 values (which I get actually but with all duplicates and what I need is just list of TWO values)

can you please advise where is the issue?

thank you