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​

 

[Arts]

Firstly thank you for taking time out on the weekend and responding really wasn't expecting that. (unless you were killing time before the rugby started )

Ok so stick with me here, where/how is the #N/A coming from? In post 5 I'm asking the OR 2 questions

1, is there an error (ISNA), 2, is the value = 0. Both of these are yes or no questions correct ? So it should only return Yes or no i.e. TRUE or FALSE

 

[Fluff]

The ISNA is on;ly on one of the index/match functions & so the other still returns #N/A as can be seen in the 2nd image you posted in post#5

 

[Arts]

I think I may have got it, are you saying (speaking in how I see it language) as John doesn't exist therefore there is no score to = 0 hence this is throwing up the #N/A.

 

[Fluff]

I'm saying that as John doesn't exist the 2nd index/match in the OR will return #N/A & so that is what the formula returns.

 

[Arts]

Got it!! I was under the thought that as John didn't exist (not in the look up) this gives us the #N/A which it does then the second aspect of the OR wouldn't be required as the first argument was met therefore giving us a score of 0 but now get the second index match is still going to run and return a #N/A...

Thank you for the help as always and even more appreciative of you responding on the weekend!!

 

[Fluff]

Glad to help & thanks for the feedback.

 

[FormR]

FWIW, you could probably reduce the number of times you calculate the INDEX(MATCH()) to 2 like this.

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

With the newer versions of Excel you'd only have to do the calculation once.