Countif and Vlookup in a single formula
 

I given the following formula as one
=IF(COUNTIF([item.xls]Sheet1!B:B,G7),VLOOKUP(G7,[item.xls]Sheet1!B:L,11,0),0)

If there is no record matching with item.xls, the result should be 0
otherwise the result will be 11th column. But it working fine except if there
is no record to match it is not giving the result as 0 in G7. Please help

Countif and Vlookup in a single formula
 

Have you confirmed that you do not have a data type mismatch. Looking for a
number is a list of text values or vice versa. Countif will find the match
but vlookup won't.
--
HTH...

Jim Thomlinson


"pol" wrote:

I given the following formula as one
=IF(COUNTIF([item.xls]Sheet1!B:B,G7),VLOOKUP(G7,[item.xls]Sheet1!B:L,11,0),0)

If there is no record matching with item.xls, the result should be 0
otherwise the result will be 11th column. But it working fine except if there
is no record to match it is not giving the result as 0 in G7. Please help

Countif and Vlookup in a single formula
 

Thanks for the reply

No datatype mismatch . Both column are text column

"Jim Thomlinson" wrote:

Have you confirmed that you do not have a data type mismatch. Looking for a
number is a list of text values or vice versa. Countif will find the match
but vlookup won't.
--
HTH...

Jim Thomlinson


"pol" wrote:

I given the following formula as one
=IF(COUNTIF([item.xls]Sheet1!B:B,G7),VLOOKUP(G7,[item.xls]Sheet1!B:L,11,0),0)

If there is no record matching with item.xls, the result should be 0
otherwise the result will be 11th column. But it working fine except if there
is no record to match it is not giving the result as 0 in G7. Please help