VLOOKUP returning a value when it shouldn't
 

I have done a V lookup, which takes an item no. (B:B), looks for it in the
'Sales analysis by items' and returns the value in column 3. Althought if
there is no item no. in the 'sales analysis' workbook then it still returns a
value.

how can i stop this and get it to leave it 0 or blank??

=VLOOKUP(B:B,'Sales Analysis by Items (Annual).txt'!$A$2:$D$222,3)

I asked yesterday and got the answer =IF(ISNA(VLOOKUP),0,(VLOOKUP))
but it wasn't working.

Thanks Nicole

VLOOKUP returning a value when it shouldn't
 

SORRY ALLL OKAY - I missed the ,0 at the end of the lookup to make sure it
only found an exact match.

Cheers
Nicole

"Nicole" wrote:

I have done a V lookup, which takes an item no. (B:B), looks for it in the
'Sales analysis by items' and returns the value in column 3. Althought if
there is no item no. in the 'sales analysis' workbook then it still returns a
value.

how can i stop this and get it to leave it 0 or blank??

=VLOOKUP(B:B,'Sales Analysis by Items (Annual).txt'!$A$2:$D$222,3)

I asked yesterday and got the answer =IF(ISNA(VLOOKUP),0,(VLOOKUP))
but it wasn't working.

Thanks Nicole

VLOOKUP returning a value when it shouldn't
 

Include the 4th argument, [range_lookup], and set it to either 0 or FALSE:

=VLOOKUP(B:B,'Sales Analysis by Items (Annual).txt'!$A$2:$D$222,3,0)

This means that you'll only get exact matches.

--
Biff
Microsoft Excel MVP


"Nicole" wrote in message
...
I have done a V lookup, which takes an item no. (B:B), looks for it in the
'Sales analysis by items' and returns the value in column 3. Althought if
there is no item no. in the 'sales analysis' workbook then it still
returns a
value.

how can i stop this and get it to leave it 0 or blank??

=VLOOKUP(B:B,'Sales Analysis by Items (Annual).txt'!$A$2:$D$222,3)

I asked yesterday and got the answer =IF(ISNA(VLOOKUP),0,(VLOOKUP))
but it wasn't working.

Thanks Nicole