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!

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.

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!

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!