Further to yesterday.... I didn't really have enough time to deal properly with this issue.
LOOKUP() can be quite difficult to understand if you aren't used to it. I'll attempt to explain, and hope that the experts here won't be to hard on my if I miss anything :smile:
The vector form has a) a lookup value [to search for in] b) a lookup vector (a range of values consisting of a single row or column of values) and c) an [optional] result vector from which to return a value in the same position as the match found in the lookup vector. If the result vector is used it must have the same number of data members as the lookup vector. If its not used, Excel will report the match from the lookup vector.
I understand that the LOOKUP function uses a binary search algorithm in order to search the data entries provided. This is very effective for searching a large amount of data quickly. but ... the data entries in the lookup vector list must be presented in ascending order. If not, then the results produced may not be as expected.
The above highlight does not apply to the result vector, but obviously it needs to be in the correct order to match with the entries in the lookup.
A final novelty is that LOOKUP is programed to return the highest value that is less than or equal to what its looking for, so if the lookup value is not found, it will return that value. If all values are above the lookup value it will return #N/A.
I rejigged your formulae, which now return the correct answers (subject to the rules outlined above):
=LOOKUP(LEFT(B12,3),{"ALC","ALT","BAC"},{"FF300","FF300","FF100"})
=LOOKUP(LEFT(B12,3),{"3SL","AL1","ALC","ALT","BAC","HL1","SLF"},{"FF200","FF400","FF300","FF300","FF100","FF400","FF200"})