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

[Ark68]

I am looking for some help with this formula ...

=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.

 

[jmacleary]

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]

Reverse the COUNTIF:

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

 

[Ark68]

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]

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

 

[Ark68]

Thanks Rory, that was the solution.

=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.

 

[Joe4]

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]

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]

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!!