Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Alternative to vlookup

    Hi All,

    Is there an alternative formula to vlookup?

    I'm using a vlookup from source data and I need to do various filters on the source data and i seems that when I do the filtering I need to change the true/false values, or is this just because I'm missing something relating to when to apply the true/false.

    Any suggests would be much appreciated.

    Regards

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Not particularly clear to me what you are doing from that, but anyway...

    VLOOKUP takes no account of filters. Nor does any lookup function (other than DLOOKUP). The True/False only determines whether you want an exact or approximate match, and whether your source data needs to be sorted in ascending order.

    Can you clarify your issue?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. The Following User Says Thank You to rory For This Useful Post:

    verada (2016-01-11)

  4. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi Rory - Sorry, I'll try to clarify further

    I use this formula =VLOOKUP($B31,Worksheet!$A$9:$AE$200,Worksheet!$H$ 3,FALSE) to achieve the results, however I filter (say AE and sort (Z-A)) then may apply a subfilter in say AD in the worksheet which then changes the order of the reference cell in column A (so these could be in any random order depending on the filtering and sorting). I think this is where I'm having problems with the results that are being returned - when i change the true to false I get different results.

    Hope this helps

    Regards

    I've used this as another option =INDEX(entry_details,MATCH($A9,entry_number,0),MAT CH(B$8,Worksheet!$A$8:$AE$8,0)), looks to work well
    Last edited by verada; 2016-01-12 at 01:44. Reason: Found Alternative

  5. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you use FALSE as the final argument, the filtering and sorting shouldn't affect the result. If you use TRUE, the table must be sorted in ascending order on column A.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. The Following User Says Thank You to rory For This Useful Post:

    verada (2016-01-12)

Posting Permissions

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