VLookups

[Anna]

I have a table of data (product codes), sorted ascendingly and sales units. I am using this to complete a look-up in another work sheet which lists these product codes, but also product codes that do not exist in the data range. I need to see sales units for the codes that are in both worksheets and zeros for those that aren't.
However, for codes which cannot be found in the range, the lookup function either gives me the sales unit for the product code closest to it, or if i use the 'false' wording at the end of the function, it gives me an #n/a.
Can anyone advise on how to either use the vlookup function to make any values it can't find zero, or to then, in the next column, use an IF function to change any values that aren't numbers (e.g. #n/a) to zero?

Help!

Thanks

Anna

[jrnyman]

Try this for your formula:

=IF(ISNA(VLOOKUP([product code],[range],[column],FALSE))=TRUE,0,VLOOKUP([product code],[range],[column],FALSE))

The ISNA() function checks if a given value is the #N/A error value. In the above function, if the value of the VLOOKUP would be an error, it returns 0, else it returns the VLOOKUP value.

HTH

[Anna]

The formula doesn't seem to work - do i type it all in one cell?

[Albert 1]

If A2:A100 in sheet 1 contents product codes, B2:B100 in sheet 1 the sales units, A2:A100 in sheet 2 the same product codes as in sheet 1 but also product codes wich don't exist in sheet 1, enter in C2 of sheet 2:
=IF(ISNA(VLOOKUP($A2,SHEET1!$A$2:$B$100,1,FALSE)),0,VLOOKUP($A2,SHEET1!$A$2:$B$100,2,FALSE))
Copy this formula down in column C of sheet 2.



[ This Message was edited by: Albert 1 on 2002-04-24 04:39 ]

[ This Message was edited by: Albert 1 on 2002-06-01 02:57 ]

[Aladin Akyurek]

On 2002-04-24 02:27, Anna wrote:
I have a table of data (product codes), sorted ascendingly and sales units. I am using this to complete a look-up in another work sheet which lists these product codes, but also product codes that do not exist in the data range. I need to see sales units for the codes that are in both worksheets and zeros for those that aren't.
However, for codes which cannot be found in the range, the lookup function either gives me the sales unit for the product code closest to it, or if i use the 'false' wording at the end of the function, it gives me an #n/a.
Can anyone advise on how to either use the vlookup function to make any values it can't find zero, or to then, in the next column, use an IF function to change any values that aren't numbers (e.g. #n/a) to zero?

Help!

Thanks

Anna

Lets say that the product codes and sales units data are in A2:B100 (excuding labels) in worksheet 1 whose name is, say, Data.

From a different worksheet, use:

=IF(COUNTIF(Data!$A$2:$A$200,C2),VLOOKUP(C2,Data!$A$2:$B$100,2,0),0)

Aladin