Quote Originally Posted by rberke
Aaron, LOOKUP function requires that the vector or array be sorted. And, you can't tell it to require an exact match. The exact match is critical in most of my lookups, so I stick to the MATCH function.

Sometimes I'll use VLOOKUP for temporary spreadsheets, but never for something that is long lasting. It is too likely that I'll come back a month later, insert a new column, and not notice that I have broken the vlookup functions.
Really? Wow... hold on... Lemme go write that down.

OK then fine, index/match in place of a VLOOKUP, data sorted, exact match... blah blah blah.

More meant as a warning against hardcoding the col offset in a vlookup. I did once notice another interesting technique with the VLOOKUP though. Lemme see if I can remember it... involved using an anchored count or something like the following...

=VLOOKUP(G1,A1:E10,COLUMNS(A11),FALSE)

I guess something along these lines would probably be safe, no?

Edit: HAH! look at that it coverted my range ref to a smiley... I'd change it, but I kind of like how the formula is laughing.