SMALL function not returning value

Question

HI,

I've been using the follow look up formula to return multiple values.

IF(value=$D$2:$D$19,IF(valiue2=$E$2:$E$19,ROW($F$2:$F$19),0),0)

which returns correct row number for where the answers are true, but when nested in a small function to get the first smallest number it returns a '0', I've just put a '1'for the k value in ;small' but would usually make this dynamic.

I have the formula working on another sheet and can't see what I'm doing different.

SMALL(IF($I$2=$D$2:$D$19,IF($J$2=$E$2:$E$19,ROW($F$2:$F$19,0),0),1)

returns 0 rather than the first number.

Any suggestions as to why it's not working.

Richard.

Answer 1

thank,

I've actually already seen it. I did figure out what I was doing wrong, I needed to return error of DIV/0

values, I was getting confused anther formula see previous reply.

Thanks for taking the trouble anyway.

RD

Answer 2

Hi Richard

Check the video below I think might help you

Regards

Jeovany

https://youtu.be/fDB1Ktyhp3Y

Answer 3

Hi,

Thanks, yes I sort of realized after posting that in the original formula I had errors not 0's.

I was getting confused with the row(range)/if(value=range,if(value2=range2,1,0,),0) where I did want 1 or zero as a divisor. But thank you anyway.

RD

Answer 4

That is because you use 0 as value_if_true. This is smaller than all the row numbers.

Use this instead:

=SMALL(IF(($I$2=$D$2:$D$19)*($J$2=$E$2:$E$19),ROW($F$2:$F$19)),1)