countif and Vlookup

[pol]

I given the following formula

IF(COUNTIF([item.xls]sheet1!A:C,G7),TRUE,FALSE) , the result is showing as
true if the record exist in item.xls . At the same time I wrote another
VLOOKUP(G7,[item.xls]sheet1!A:C,3,0) the result is showing as '#NA' for the
same record which already showed as true in Countif .

Please anybody can help me to get the reason for the diffrent result in
both function;.

 

[Luke M]

VLOOKUP is looking for G7 in only column A (as this is the nature of how
VLOOKUP works). Your COUNTIF is looking at all 3 columns, A:C. So, if G7 is
found in C6, your first formula is true, but it causes an error in the
VLOOKUP. A better check would be to limit your COUNTIF to just A:A.

 

[Jim Thomlinson]

Countif treats everything as text. That makes it very valuable when doing
lookups or matches. The reason is that countif will let you know if you have
a data type mismatch (quite possibly what you have). That is you are looking
up a number in values stored as text or vice versa. I personally always use
countif with my lookups or matches...

if(countif(...) > 1, "multiple values", if(countif(...) = 0, "not Found",
Lookup/match)

This is relatively foolproof. If it counts multiple values then it returns
"multiple values". If it count none then it returns "not found". If it return
#N/A then I know that the value exists but that I have a data type mismatch.