lookup function need assistance

MOEMOE

New member
Joined
Apr 7, 2018
Messages
3
Reaction score
0
Points
0
I have more than just the 3 lookup vector and of course with more or maybe same result vector but it seems to be only reading the first three even after adding a 4th, 5th and so on.

=LOOKUP(LEFT(B12,3),{"ALT","ALC","BAC"},{"FF300","FF300","FF100"}) this is what I have to settle for however below is what I want.

=LOOKUP(LEFT(B12,3),{"ALT","ALC","BAC","AL1","3SL","SLF","HL1"},{"FF300","FF300","FF100","FF400","FF200","FF200","FF400"})

Or is there another function that would work better.
 
There is no reason why your second formula wouldn't work as long as you conform with the rules associated with LOOKUP(). The problem with your rendition above is that the look-up vector (i.e. the first array) has to be in ascending order, or it won't match the lookup values correctly. For example, your first look up vector would need to be {"ALC","ALT","BAC"}.
LOOKUP() does have its uses, but it has been largely superseded by VLOOKUP and HLOOKUP, which provide more flexible user control.
 
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"})
 
Thank you, I will try this out and let you know my results.
 
Back
Top