There are cases where it is useful. Like I want to retrieve the FX rate for a given day but my time series might be missing days, so finding the nearest result is practical.

Also the default assumes that the values are in ascending order, which is computationally efficient as it can do a binary search.

But very dangerous in the most common scenario.

What would be even better is to be able to tell Excel to create a hash table / dictionary in memory from a table so that it can be queried very efficiently.

But as I posted everywhere on this page, Microsoft doesn't seem to be minded to add new features. Changing the color scheme between versions, making minor improvements, keeping bugs as they are, incrementing the version and shipping it!

Oh I never thought about using vlookup for anything but categorical values, but yeah your example makes sense.

I like the new functions that make you not having to wrap everything in ISNA(), like ISERROR()...

It's great to categorize number ranges, like people's age for example.

You have a line with each person and their age.

In you list you have age groups, looking like

    Age Age Group
    ...
    19  10-19
    29  20-29
    39  30-39
    49  40-49
    ...

Then you use vlookup to find the group that matches the age, and the non-strict will match 12 to 19, 33 to 39 and so on, returning the age group for that value.

If you have large sheets, you might also care that a strict match necessarily involves a linear search, O(N), while a "non-strict" match uses a binary search, O(log N).

Strict search is slower, in other words. You just need to know what you want, really. (Even if you want a strict match, it might be faster to sort once, then do non-strict match, then compare for identity).