Formula To Check Whether A Value Exists In A Range Is Reporting Incorrectly

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,812
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I am looking for some help with this formula ...

Code:
=IF(COUNTIF(D182,[Rental_Detail.xlsm]Rental_Data!$A:$A) >0, "YES","NO")"

D182 = 84351

This value does exist in workbook Rental_Detail.xlsm, worksheet Rental_Data, column A ... but the formula is wrongly saying it doesn't.
 
Last edited:

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,054
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Hi there. Check the cell type set for column A and D182. If they are different, then I suspect it's because numeric and text don't necessarily match even when to the eye they look the same.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,133
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Reverse the COUNTIF:

=IF(COUNTIF([Rental_Detail.xlsm]Rental_Data!$A:$A,D182) >0, "YES","NO")
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,812
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thank you John, so if I did what I should to ensure that, I set the format of both columns to "Number", 0 decimal points.
No, the formula results in yes for everything, even when the value isn't in the list.
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,054
Office Version
  1. 365
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

OOPS Rory is right - I didn't look at your formula properly. Do what he says.
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,812
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks Rory, that was the solution.

Code:
=VLOOKUP(D410,[Rental_Detail.xlsm]Rental_Data!$A:$D,3,FALSE)

This is also giving me problems. All I get is #N/A even when the formula should work. I tried playing with it similar to your suggestion for my OP, but that gave me a #VALUE error.
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,968
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

All I get is #N/A even when the formula should work
That simply means it is not finding a match for the value in D410.
Note that it much match EXACTLY.
Any extra spaces in one will cause it not to match.
Any you cannot compare a number to a "number entered as text".
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,133
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Any you cannot compare a number to a "number entered as text".

I suspect that is the issue. COUNTIF will treat them the same, but VLOOKUP (and MATCH) will not.
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,812
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
That was the issue ... one of the two columns was likely text. I'm not sure which one, but I converted them to numbers, and all seems well.
Thank you all!!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,110
Messages
5,599,773
Members
414,336
Latest member
Nicolas2465

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