Essentially - from a work flow perspective:
- a SEARCH is conducted for each term listed in B2:B60 against the value in A2 (note: A2 & B2 rather than A1 & B1 - row 1 can be ignored)
- SEARCH will return a Number (found) or #VALUE! (not found)
- LOOKUP in this instance will find the last number listed in our lookup_vector (search results) and return the associated value from our result vector (state list)
LOOKUP works per the above because:
- our criteria value is a VERY big number (9.99*10^307)
- LOOKUP assumes all values in the lookup_vector are ascending order (irrespective of reality)
- LOOKUP ignores values in the lookup_vector not of the same data type as our criteria (including errors)
- LOOKUP retrieves the last value <= criteria from lookup_vector (or associated result_vector result where specified)
Note:
Given your examples you could try:
which would negate need for B2:B60 altogether.
Also, when using this approach it's important to note that blanks in the lookup_vector would cause odd results - so a more robust method would be:
Bookmarks