Formula retuning #Value!

[Liz_I3]

Hi
The below formula was working until I added the And for a second condition.
Names, Man and Cat are all name ranges on sheet 2. The formula is on sheet 1
I did enter is with the ****f,Ctrl , Enter but it is returning a #Value!

What am I missing?

=IF(ROWS(U$3:U3)<=$T$3,INDEX(Names,SMALL(IF(AND((Man=$U$2,Cat=$T$2),ROW(Man)-ROW(sheet2!$O$3)+1),ROWS(U$3:U3)),1),""))

Thanks
L

 

[number kruncher]

Having 'two' Paranthesis after AND(( does not look right.

Example;

=IF(AND(A1=x,B1=y),TRUE,FALSE)

 

[Liz_I3]

Thanks that got rid to the #Value!, but now it is not returning the correct value. It is just selecting the first name in the range. It should be looking for only the name that match my 2 coditions.

=IF(ROWS(U$3:U3)<=$T$3,INDEX(Names,SMALL(IF(AND(Man=$U$2,Cat=$T$2),ROW(Man)-ROW(Sheet2!$O$3)+1),ROWS(U$3:U3)),1),"")

L

 

[number kruncher]

Could you supply an example of the data?

 

[jmthompson]

Try stepping through the formula with Formula Evaluate. Can be very useful to help identify where a parenthesis belongs or not

 

[Liz_I3]

If I copy the formula down to the next row instead of getting a name I get the #Num!. When I click on the trace formula it tells me it has encountered existing arrow or a circular reference. I do not see a circular reference and do not know how to remove the arrows

L

 

[Aladin Akyurek]

Hi
The below formula was working until I added the And for a second condition.
Names, Man and Cat are all name ranges on sheet 2. The formula is on sheet 1
I did enter is with the ****f,Ctrl , Enter but it is returning a #Value!

What am I missing?

=IF(ROWS(U$3:U3)<=$T$3,INDEX(Names,SMALL(IF(AND((Man=$U$2,Cat=$T$2),ROW(Man)-ROW(sheet2!$O$3)+1),ROWS(U$3:U3)),1),""))

Thanks
L

Try...

=IF(ROWS(U$3:U3)<=$T$3,INDEX(Names,SMALL(IF(Man=$U$2,IF(Cat=$T$2,ROW(Man)-ROW(INDEX(Man,1,1))+1)),ROWS(U$3:U3)),1),""))

T3 must house something like:

=SUM(IF(Man=$U$2,IF(Cat=$T$2,1)))

Both formulas need confirming with control+shift+enter...

 

[Liz_I3]

Thanks Aladin
That worked!!!! I was concentrating so hard on the Index formula I never gave any attention to my countif which is in T3. It was only counting the Man field not the Cat

Thanks again
L