+ Reply to Thread
Results 1 to 6 of 6

Problem with Lookup Function and Sorting

  1. #1
    Registered User
    Join Date
    11-27-2018
    Location
    United States
    MS-Off Ver
    1803
    Posts
    3

    Problem with Lookup Function and Sorting

    I apologize if this has been asked before / an answer is posted somewhere already. I was unable to find what I'm looking for myself, but I could be wording my search wrong.

    I have a lookup function on a sheet called 'Measures' that grabs a number from its respective row (stored in column A) and looks for the same number on a different sheet called 'Risks' (also in column A). Here's an example of this function:

    Please Login or Register  to view this content.
    This works exactly as intended, until I sort column A on the 'Risks' sheet in any way other than smallest to largest. For example, if I sort column A on the 'Risks' sheet largest to smallest, the lookup functions on the 'Measures' sheet start displaying the wrong information, duplicates, and "#N/A", with no pattern or reasoning that I can see. I haven't been able to determine why these lookup functions return weird results like this, all the information is still there and still in the specified lookup area, so theoretically the values returned by the lookup functions shouldn't change. I'm not very experienced with Excel, so there could very well be something obvious that I'm missing here.

    I understand that Excel isn't a database, and that I might be better off using a database for something like this, but I'm locked into using excel for the moment due to things outside of my own control.

    If anymore information is required to figure out the issue, please let me know and I'd be happy to provide.

    Any help is greatly appreciated. Thanks!

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,898

    Re: Problem with Lookup Function and Sorting

    LOOKUP uses binary search so relies on data being sorted (as you outline) -- it can be useful on unsorted data too, but that's one for another day.

    In the above scenario you need to use VLOOKUP with an Exact match flag... so your LOOKUP becomes

    =VLOOKUP(A4,Risks!A:B,2,0)

    the 0 {or FALSE} at the end effectively tells XL data is unsorted, and you're looking for the criteria value {not nearest match} -- as such it won't apply the binary search approach.

    Binary Search is very useful when dealing with huge datasets as only a fraction of values are looked at... unlike an exact match.

    Unless your file is hideously complicated I doubt you will notice the difference between your current LOOKUP and the above VLOOKUP approach.

  3. #3
    Registered User
    Join Date
    11-27-2018
    Location
    United States
    MS-Off Ver
    1803
    Posts
    3

    Re: Problem with Lookup Function and Sorting

    This works, thanks! However, now I've discovered another issue, which might be similar. When the 'Risks' sheet is sorted, the value of Index functions (with Match functions within) change.

    I only noticed this out of luck, it's much more subtle. One of the numbers returned by an Index + Match is 5 when sorted smallest to largest, but changes to a 3 when sorted largest to smallest.

    Here's the function

    Please Login or Register  to view this content.

    This one's a bit more complex, so hopefully that doesn't effect being able to fix it.

    Thanks again for the help!

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,898

    Re: Problem with Lookup Function and Sorting

    So, as you have outlined, the INDEX return will change on grounds MATCH criteria cell (b12) has itself changed.

    What do you want to happen, exactly?

    If you need this to persist with returning data relative to ascending MATCH criteria you can, perhaps, use SMALL for your criteria, e.g.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    as the above is copied down the SMALL will increment from 1 to 2 to 3 etc... and thus your criteria is still being calculated in 0-9 order

    Note: references to A2 above is purely for illustrative purposes, change all A2 references in the above to be the first cell in which the above formula will appear.

    EDIT: use of SMALL obviously works on assumption that Risks!B:B contains numerics... if not, i.e. strings, or mix of data types, let us know.
    Last edited by XLent; 11-27-2018 at 02:35 PM.

  5. #5
    Registered User
    Join Date
    11-27-2018
    Location
    United States
    MS-Off Ver
    1803
    Posts
    3

    Re: Problem with Lookup Function and Sorting

    So the 'Assets' sheet is a sheet containing the names of a bunch of devices (computers, firewalls, etc.) along with information about them. 'Assets!$A$1:$W$77' encompasses all the information, 'Asset!B' is where the name of the device that particular row refers to is held, and 'Risks!B' also holds the names of devices. The function grabs the device name associated with it's row on the 'Risks' sheet, looks in 'Assets!B' for a matching name, then grabs the important value from that row (we'll call it Total for the sake of simplicity).

    When I sort the 'Risks' sheet, I want it to continue displaying the total for that device. This is another case where I don't really understand how it broke considering it's still looking for the same name as it was before I changed it's sorting. It may also be worth noting that all the information in a specific row stays together, so the information in row #10 will stick together if it gets moved to row #20 for example. so none of the information is changing.

    After writing all this I realized that when the information gets sorted the function doesn't update the cells that it's looking at. So a function (on the 'Risks' Sheet) that has to grab the asset name from 'Risks!B12' will still be getting the name from 'Risks!B12' after sorting, instead of updating to the new row it's on to grab the information associated with it. I'm not sure if this is the cause or if there's something else also at play here, so I'll leave my explanation in case it proves useful.


    Hopefully I explained all that well enough, let me know if you need any additional information.

    Thanks!

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,893

    Re: Problem with Lookup Function and Sorting

    Hello ItsRoland and Welcome to Excel Forum.
    I might help us in our effort to help you if you could upload a desensitized sample of your workbook by clicking on the GO ADVANCED button below the Quick Reply window and then scrolling down to Manage Attachments to open the upload window.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Problem using LOOKUP function.
    By josel2820 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-09-2013, 04:53 PM
  2. Lookup problem when sorting data in a table
    By kevinp in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-19-2011, 06:51 AM
  3. Problem with Lookup-function
    By SamMy in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-03-2006, 09:15 AM
  4. Lookup function problem (kg)
    By Max in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-06-2005, 12:05 PM
  5. Lookup function problem (kg)
    By greencecil in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  6. problem lookup function
    By RagDyeR in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 06:05 AM
  7. problem lookup function
    By bill gras in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  8. problem lookup function
    By bill gras in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1