Vlookup - Maybe

RodneyC

Board Regular
Joined
Nov 4, 2021
Messages
240
Office Version
  1. 2016
Platform
  1. Windows
I'm trying to use VLOOKUP where if the condition is met, return a value of Y. If it's not met, return a value of N

This produces the Y but produces the #N/A value if the condition is not met. How do I modify my formula so it produces N instead on #N/A?

Thank you in advance
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,486
Office Version
  1. 365
Platform
  1. Windows
You could do something like:
Excel Formula:
=IFERROR(VLOOKUP(...),"N")

But I think I could probably use a COUNTIF instead, i.e.
Excel Formula:
=IF(COUNTIF(...)>0,"Y","N")
 
Solution

RodneyC

Board Regular
Joined
Nov 4, 2021
Messages
240
Office Version
  1. 2016
Platform
  1. Windows
You could do something like:
Excel Formula:
=IFERROR(VLOOKUP(...),"N")

But I think I could probably use a COUNTIF instead, i.e.
Excel Formula:
=IF(COUNTIF(...)>0,"Y","N")
YAHTZEE! The first option worked like a charm. Thank you
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,486
Office Version
  1. 365
Platform
  1. Windows
You are welcome.

The reason I prefer the second option is because the first will ignore ANY error, not just an #N/A error.
So you could ignore other errors that you may want to know about.
 

RodneyC

Board Regular
Joined
Nov 4, 2021
Messages
240
Office Version
  1. 2016
Platform
  1. Windows
Thanks for the follow up. I've entered this

=IF(COUNTIF(B4,InclusionList!$A$2:$E$32,5,FALSE)>0,"Y","N")

and it's telling me "You've entered too many arguments for this function"

There is a shortcoming of the VLOOKUP solution that if we get the COUNTIF working, I'll outline in a separate post.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,486
Office Version
  1. 365
Platform
  1. Windows
The COUNTIF function structure is NOT the same as the VLOOKUP strucutre. So you cannot just swap out the two words! You need to put the correct arguments in the correct place.
See: MS Excel: How to use the COUNTIF Function (WS)

So I think your formula will need to look like:
Excel Formula:
=IF(COUNTIF(InclusionList!$A$2:$A$32,B4)>0,"Y","N")
 

RodneyC

Board Regular
Joined
Nov 4, 2021
Messages
240
Office Version
  1. 2016
Platform
  1. Windows
The COUNTIF function structure is NOT the same as the VLOOKUP strucutre. So you cannot just swap out the two words! You need to put the correct arguments in the correct place.
See: MS Excel: How to use the COUNTIF Function (WS)

So I think your formula will need to look like:
Excel Formula:
=IF(COUNTIF(InclusionList!$A$2:$A$32,B4)>0,"Y","N")
That is working perfectly. I'll post the new question in a new post in a couple minutes I appreciate the help
 

Forum statistics

Threads
1,185,975
Messages
5,955,096
Members
438,181
Latest member
aaronstone

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
Top