IF vlookup with multiple choices

[dreximgirl]

I am trying to do an IF vlookup with an OR but keep hitting a wall. I have a spreadsheet with Yes No and N/A answers and I am trying to make it so that if the answer is Yes or N/A it brings back the word Compliant.

I created this =IF(OR(VLOOKUP(A3,'Audit Results '!$I:$AT,8) ="Yes", VLOOKUP(A3,'Audit Results '!$I:$AT,8) ="N/A"),"Compliant","Non-Compliant") but it brings back 'Non-Compliant' when it hits an N/A and I can't work out how to fix it

Any help would be gratefully received

Thanks

Lisa

 

[Joe4]

Welcome to the Board!

By "N/A", do you mean the "#N/A" error that is returned when it does NOT find a match?
Do you actually want it to return a "Compliant" when no match is found?

 

[dreximgirl]

Sorry, when I say N/A I mean that is one of the responses that has been entered in the main dataset - so in my main spreadsheet the data I have as answers to questions asked are Yes No or Not Applicable If the answer is Yes or Not Applicable then they are compliant. Does that make sense?

 

[Joe4]

Try this formula:

=LET(x,XLOOKUP(A3,'Audit Results'!$I:$I,'Audit Results'!$P:$P,""),IF(OR(x="Yes",x="N/A"),"Compliant","Non-Compliant"))

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IF(OR(VLOOKUP(A3,'Audit Results '!$I:$P,8,0) ="Yes", VLOOKUP(A3,'Audit Results '!$I:$P,8,0) ="N/A"),"Compliant","Non-Compliant")

 

[dreximgirl]

Hi & welcome to MrExcel.
How about

=IF(OR(VLOOKUP(A3,'Audit Results '!$I:$P,8,0) ="Yes", VLOOKUP(A3,'Audit Results '!$I:$P,8,0) ="N/A"),"Compliant","Non-Compliant")

Thank you so much! This works perfectly!

You are a star xx

 

[Joe4]

Glad you got it working the way you want, but I am curious. Did my formula not work for you, or did you not try it?

 

[dreximgirl]

Glad you got it working the way you want, but I am curious. Did my formula not work for you, or did you not try it?

Apologies I did try yours but it just ended up putting Compliant in all of the cells even on the No's

 

[Joe4]

Apologies I did try yours but it just ended up putting Compliant in all of the cells even on the No's

That is odd - it worked for me. I tried a variety of "Yes", "No", "N/A", and other values.
It only returned "Non-Compliant" if there was a "No" or no match at all.