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?

 

[Aladin Akyurek]

What is the look up value in the following formula you posted?

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

 

[FrancisM]

The value it is looking for is Yes in Range O4:R4.That 45 is a typo.

 

[Aladin Akyurek]

The value it is looking for is Yes in Range O4:R4.That 45 is a typo.

Is there any other value in O4:R4 except Yes or No?

Suppose we have:

O4 = Yes
P4 = No
Q4 = No
R4 = No

Wat would be the expected return for the above situation?

 

[FrancisM]

Is there any other value in O4:R4 except Yes or No?

Suppose we have:

O4 = Yes
P4 = No
Q4 = No
R4 = No

Wat would be the expected return for the above situation?

If there is a yes in any of the cell range, the answer for cell N4 would still be no. The situation you mentioned is very possible.

 

[Aladin Akyurek]

If there is a yes in any of the cell range, the answer for cell N4 would still be no. The situation you mentioned is very possible.

Looks like you want:

=IF(SUM(COUNTIFS(O4:R4,{"yes","no"})),IF(COUNTIFS(O4:R4,"no"),"No","Yes"),"no data")

 

[FrancisM]

The formula works. just not as I thought it would. The only answer that you will have in Range O4:R4 is either Yes or no. N4 should only fill with "No" if there is a Yes in O4:R4. The formula as written is copying the answer from that rang. Ie. If here is an Yes in the 4:N4 range, it then fills N4 with that answer.

 

[Aladin Akyurek]

The formula works. just not as I thought it would. The only answer that you will have in Range O4:R4 is either Yes or no. N4 should only fill with "No" if there is a Yes in O4:R4. The formula as written is copying the answer from that rang. Ie. If here is an Yes in the 4:N4 range, it then fills N4 with that answer.

Is there just one value in O4:R4 at any time?

 

[FrancisM]

The only one I track is Yes. The example you stated earlier is possible.
O4=yes
P4=No
Q4=No
R= No
I have the cell conditionally formatted to hide the No responses, but if the user hovers over the cell, the response s there. Ideally if there is a yes response between O4 & R4 then NO populates ell N4.

 

[Aladin Akyurek]

The only one I track is Yes. The example you stated earlier is possible.
O4=yes
P4=No
Q4=No
R= No
I have the cell conditionally formatted to hide the No responses, but if the user hovers over the cell, the response s there. Ideally if there is a yes response between O4 & R4 then NO populates ell N4.

What is wrong with the following?

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