IF SEARCH Function

that is the largest number Excel can use....

Lookup syntax: =LOOKUP(Lookup_value,lookup_vector,[result_vector])

in this case we are using LOOKUP to find that number in the defined lookup_vector and return the correspond item in the result_vector.

Lookup won't find that number in the lookup_vector defined by SEARCH($B$10:$B$49,B3), which returns a vector of #VALUE errors where matches are not found and some numbers where matches are found, so it looks for the last number in the vector, ignoring the errors, and returns the corresponding item in the adjacent column.

If you go to Formulas tab|Evaluate formula, you can step through to see how it works.