Formula Help OR/ISNA

[Arts]

Hi all

I am trying to do an OR statement to include if a value returns back #N/A or 0 (if a value is found of the look up but there is no value to return, hope that makes sense)

So Harry exists but has no score so this returns 0

I was successful with the ISNA and the 0 individually but cant seem to merge them.

The formula I attempted was =IF(OR(ISNA(INDEX($D$3:$D$5,MATCH(F5,$C$3:$C$5,FALSE))),INDEX($D$3:$D$5,MATCH(F5,$C$3:$C$5,FALSE))=0),"",INDEX($D$3:$D$5,MATCH(F5,$C$3:$C$5,FALSE))) but John still seems to return #N/A

Any help on this would be welcome as always and if possible a reason why my formula failed, I really thought I had it...

Name	Score		Lookup	Return
James	56​		James	56​
Fred			Harry
Harry			John	#N/A​

 

[Fluff]

How about

=IF(ISNA(INDEX($D$3:$D$5,MATCH(F3,$C$3:$C$5,FALSE))),"",IF(INDEX($D$3:$D$5,MATCH(F3,$C$3:$C$5,FALSE))="","",INDEX($D$3:$D$5,MATCH(F3,$C$3:$C$5,FALSE))))

 

[Arts]

How about

=IF(ISNA(INDEX($D$3:$D$5,MATCH(F3,$C$3:$C$5,FALSE))),"",IF(INDEX($D$3:$D$5,MATCH(F3,$C$3:$C$5,FALSE))="","",INDEX($D$3:$D$5,MATCH(F3,$C$3:$C$5,FALSE))))

Seems spot on! Thank you!

Would you be kind enough to explain where my OR/ISNA combo failed? When I use the evaluate tool it seems to give me TRUE for the first OR but then shows #N/A for the second. But when doing then separately they both work, really thought using the OR would merge them both giving me what I needed.

 

[Fluff]

It's because you get #N/A so the OR function returns that error.

 

[Arts]

I think it's this that's throwing me

When I break down the formula both are giving back TRUE or FALSE which is what I would want

But on John I get an #N/A where in my head I'm thinking if an #N/A is returned the ISNA would pick this up and insert the ""

I think i need to log of and get out the office been looking at this so long I'm at the cant see the wood for the trees stage I think

 

[Fluff]

If anything inside OR returns an error (like the 2nd Index/Match) then OR will return that error.

 

[Arts]

Yeah I don't think it computing...

For James none are true and I get a FALSE which is fine (No #N/A), why would I not get a FALSE for John but instead get an #N/A, in a sense John is also FALSE

 

[Arts]

message deleted due to error

 

[Arts]

Yeah I don't think it computing...

For James none are true and I get a FALSE which is fine (No #N/A), why would I not get a FALSE for John but instead get an #N/A, in a sense John is also FALSE

View attachment 97036

After wrestling with this on a Saturday I still seem to miss the mark.

If my last response could be ignored as it was late on Friday. After looking at this now what I mean to say is


For James none are true and I get a FALSE which is fine (No #N/A and nothing =0) so my OR would be (FALSE, FALSE)

For John we have a #N/A as he is not in the look up so and there is no score equaling 0

So I would expect my OR to say

(TRUE (as there is an #N/A), TRUE (as the result doesn't =0)

Fluff mentioned "If anything inside OR returns an error (like the 2nd Index/Match) then OR will return that error." - isn't the point to get an #N/A so the ISNA would pick this up and the OR return TRUE

 

[Fluff]

The ISNA does pick up the error as can be seen in your image in post#5, but you can also see in that image there is a #N/A error & that is what the OR function returns