# Problem with Lookup Function and Sorting

1. ## 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. ## 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. ## 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. ## 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:
`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.

5. ## 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. ## 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.

##### Users Browsing this Thread

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

#### 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