VLOOKUP formula not working.

[FrancisM]

I have a VLOOKUP in cell F5 that fills the cell with the word No, if "No Show", or other sect text is in cell L5. The formula below works as expected, but I am trying to adapt it to another workbook.

=IFERROR(VLOOKUP(L5,{"No Show","No";"Not Interested","No";"Veteran Canceled","No";"Consult Canceled","No";"Transferred to SE","No";"Not Eligible","No";"Referred to SE","No"},2,0),"")

I am trying to have a similar formula where Yes is in cell range O4:R4, will cause “No” to fill N4.

=IFERROR(VLOOKUP(O4:R45,{"Yes","No"},2,0),"")

I have tried this formula but is is not working. Any advice as to what I am doing wrong?

 

[FrancisM]

I just tried your newest formula. I found that if Yes is entered, the is nothing in cell N4. If No is entered, There is ?. I need to have No in N4. If there is a Yes response in the range indicated.

 

[Aladin Akyurek]

I just tried your newest formula. I found that if Yes is entered, the is nothing in cell N4. If No is entered, There is ?. I need to have No in N4. If there is a Yes response in the range indicated.

You have to enter the formula

=IF(COUNTIFS(O4:R4,"Yes"),"NO","?")

in N4.

Replace comma with a semi-colon if you are on a non-American system.

This formula returns NO whenever one or more Yes's in O4:R4.​

 

[FrancisM]

Thank you Aladin Akyurek. That worked. I replaced ? with "".
1 final question. Can you lead to a link where I can combine this with similar formula. =if(countifs(O4:R,"No"), "Yes","")

 

[Aladin Akyurek]

Thank you Aladin Akyurek. That worked. I replaced ? with "".

You are welcome.

{quote]1 final question. Can you lead to a link where I can combine this with similar formula. =if(countifs(O4:R,"No"), "Yes","")[/QUOTE]

How do you mean? Try to word your intention.

 

[FrancisM]

Sorry being so unclear. I just learned that I need a yes/no response for the range.
If the response "Yes" in that range N4 is populated with NO, & "No" in the range N4 is populated with Yes.

 

[Aladin Akyurek]

Sorry being so unclear. I just learned that I need a yes/no response for the range.
If the response "Yes" in that range N4 is populated with NO, & "No" in the range N4 is populated with Yes.

O4 >> no
P4 empty
Q4 >> yes
R4 >> no

What is the result?

 

[FrancisM]

Here is are some examples. O4 is populated “no”, P4is empty, Q4 is populated with “Yes”,R5 is populated with “Yes”; the expectedresponse for N4 is “No”.
O5 is populated “Yes”,P4 is empty, Q4 is populated with “Yes”, R5is populated with “Yes”; the expected response for N4 is “No”.
O6 is populated “no”,P4 is empty, Q4 is populated with “NO”, R5is populated with “No”; the expected response for N4 is “Yes”.

I have tried this formula =IF((AND(O5:R5="yes",O5:R5="No")),"No","Yes") The response I get is #VALUE !

 

[Aladin Akyurek]

Would you please stop posting non-working formulas? Instead try to word your goal...

yes, yes, no, no : fill in for this row the expected result.

Give more examples the same way. Try to be exhaustive.

 

[FrancisM]

Sorry. I found I had some typos in the last posting. Here is the corrected posting.
O4 is populated “No”, P4is empty, Q4is populated with “Yes”,R5 is populated with “Yes”; the expectedresponse for N4 is “No”.
O5 is populated “Yes”,P5 is empty, Q45is populated with“Yes”, R5is populated with “Yes”; the expected response for N4 is“No”.
O6 is populated “No”,P6 is empty, Q6 is populated with“NO”, R6 is populated with “No”; the expected response for N4 is“Yes”.
The user would enter their responses in O through R, & the samples above are the actual possible responses & expected responses.

 

[Aladin Akyurek]

Try...

=IF(COUNTIFS(O4:R4,"Yes"),"NO",IF(COUNTIFS(O4:R4,"No"),"YES",""))​