countif and Vlookup

P

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;.
 
L

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top