Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: VLookups

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    390
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,030
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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




Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •