VLOOKUP formula not working.

FrancisM

Board Regular
Joined
Apr 12, 2016
Messages
139
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.

Code:
=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?
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
What is the look up value in the following formula you posted?

=IFERROR(VLOOKUP(O4:R45,{"Yes","No"},2,0),"")
 
Upvote 0
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.
 
Upvote 0
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")
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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","?")
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top