Weird problem with Lookup function

[ammylar5]

This my formula: =LOOKUP(H2,Advisors!B:B,Advisors!C:C)
This is the result:

The Advisorid = 1 is also a formula which refer to a different sheet.
If I change the from a formula by entering the # 1 - the lookup works

However if instead of entering the 1 I do a copy the column and past value the formula will not work.
Weird but here it is. If you can help great, I really don't want to have to enter a # in the column by hand, too many rows.

 

[Fluff]

Hi & welcome to MrExcel.
What is the formula in H2?

 

[joeu2004]

Probably a difference in data type: text vs numeric. Looks can be deceiving, and the cell formatted does not matter.

Use =ISTEXT(H2) and =ISTEXT(B123) to determine data type, assuming B123 is the intended match.

And if both are text, compare =LEN(H2) and =LEN(B123) to be sure there are no extraneous characters that you are overlooking.

Also note that LOOKUP (sic) expects column B to be in ascending order.

You might want to consider using VLOOKUP(H2, Advisors!B:C, 2, FALSE) to force an exact match without requiring ordered data in column B.

Or equivalently: INDEX(Advisors!C:C, MATCH(H2, Advisors!B:B, 0)).

PS: I deprecate the use of full-column references like B:B. $B$1:$B$1000 is better, changing 1000 if necessary.

 

[ammylar5]

Probably a difference in data type: text vs numeric. Looks can be deceiving, and the cell formatted does not matter.

Use =ISTEXT(H2) and =ISTEXT(B123) to determine data type, assuming B123 is the intended match.

And if both are text, compare =LEN(H2) and =LEN(B123) to be sure there are no extraneous characters that you are overlooking.

Also note that LOOKUP (sic) expects column B to be in ascending order.

You might want to consider using VLOOKUP(H2, Advisors!B:C, 2, FALSE) to force an exact match without requiring ordered data in column B.

Or equivalently: INDEX(Advisors!C:C, MATCH(H2, Advisors!B:B, 0)).

PS: I deprecate the use of full-column references like B:B. $B$1:$B$1000 is better, changing 1000 if necessary.

Thank you so much for taking the time. I understood everything you said and had already done trying to solve the problem but could not solve the problem. So I thought that may be my workbook was corrupted. I created a brand new workbook and used the VLOOKUP as you suggested and it worked. Thank you so much.