LOOKUP skips cells

B

Bernard Liengme

Please explain "skipping".
Tell us what the data looks like and what formula you are using.
best wishes
 
R

reddog_3d

Here's my formula:

LOOKUP(A1, Main!$A$90:$A$99, Main!$J$90:$J$99)

what I'm trying to do is tell excel to check if A1 matches anything on
another workbook, if it does, then look at this column and return the
corresponding value in this column.

But for some weird reason it keeps skipping the first two cells in the
range! Like if A1 =Main!A90, it returns Main!J93. Or if A1 =Main!A91, it
returns Main!J94.

This formula works everywhere else - just not on these two cells (Main!A90
and Main!A91)!!!!

I've deleted everything and re-written from fresh, still does it.

If excel had a butt I'd kick it...

I've deleted
 
T

T. Valko

LOOKUP requires that the lookup_vector be sorted in acsending order. If it
is sorted and there is not an exact match found then it will match the
closest value that is less than the lookup_value. If you're wanting exact
matches only try this alternative:

=INDEX(Main!$J$90:$J$99,MATCH(A1,Main!$A$90:$A$99,0))
 
R

reddog_3d

"10" is the column index. Does excel know I'm referring to another workbook
(Main!) and not the workbook A1 is in?
 
R

reddog_3d

This works!

excel survives the hangman for one more day...

awesome tool when it works, but when it doesn't I'd rather poke myself in
the eye with a stick than try to figure it out.

Thanks for all ya'lls help!
 
Top