Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using LOOKUP (Excel 2000)

    Hi,

    I am trying to use the LOOKUP functions to match to tables. I have 2 sheets, one contains orders from system x and another sheet which contains the same orders from system y, both lists are not the same because system y occasional looses the orders. So I am trying to do a LOOKUP using system x as the key and finding orders in system y, thus finding the gaps. I have used VLOOKUP but the results are not mapping correctly.

    I one case when the match fails from that point I get N/A, in another case I get different order numbers.

    Any guidance on how to use LOOKUPs and will this do what I want.

    Any help thanks.

    Mike
    PS - Where has the Search option gone on the Lounge

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using LOOKUP (Excel 2000)

    In the first place, look at <post#=250092>post 250092</post#> and <post#=253135>post 253135</post#> about the temporary suspension of the search facility, and future changes in the Lounge.

    To use VLOOKUP to find exact matches, you must set the fourth argument to FALSE. If you omit this argument, or set it to TRUE, VLOOKUP looks for the largest value in the lookup range that is less than or equal to the search value. The lookup range must be sorted in ascending order for this to work correctly. If you set the argument to FALSE, VLOOKUP looks for an exact match, and returns #N/A if there is no exact match.

    Another thing to watch is that the lookup range must be an absolute reference or a named range if you want to be able to fill down the formula to other cells. If you have =VLOOKUP(Sheetx!A1,Sheety!A1:B999,2,FALSE) in a cell, and fill it down, you'll get =VLOOKUP(Sheetx!A2,Sheety!A2:B1000,2,FALSE). The reference to Sheetx!A2 is OK, but the reference to Sheety should not have shifted. Instead, use =VLOOKUP(Sheetx!A1,Sheety!$A$1:$B$999,2,FALSE) in the first cell.

    Post back if you need more help.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using LOOKUP (Excel 2000)

    Thanks Hans,

    That did the trick...............

    Regards

    Mike

Posting Permissions

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