LOOKUP Function

[Denny Leung]

Dear all,

In sheet 1, I've 3 columns of data. In sheet 2 column A, I
want to set a LOOKUP formula for the result of column B,
but it doesn't work. Please help. For example in sheet 2:

Cell A1 :

=LOOKUP(B1,sheet1!A:B)

However, in cell C1:

=LOOKUP(B1,sheet1!B:C) - this formula works!! I don't know
why. Please advise.

Thanks in advance

 

[Suresh Nair]

Denny

Lookup takes one row ,one column into account. Probably
the b1 in sheet2 exist in col b in sheet1 and hence
nothing to look ahead in the array A:B.
HTH

 

[Random]

You may want to look into VLOOKUP instead. It is designed to handle
multiple columns.

 

[Sandy]

Denny:

Try the following:

=IF(ISNA(VLOOKUP(A1,Sheet1,2,FALSE))=TRUE,0,VLOOKUP
(A1,Sheet,2,FALSE))

The "2" above means "B"

Hope this helps!

Sandy

 

[Harlan Grove]

In sheet 1, I've 3 columns of data. In sheet 2 column A, I
want to set a LOOKUP formula for the result of column B,
but it doesn't work. Please help. For example in sheet 2:

Cell A1 :

=LOOKUP(B1,sheet1!A:B)

However, in cell C1:

=LOOKUP(B1,sheet1!B:C) - this formula works!! I don't know
why. Please advise.

In the first formula, LOOKUP searches for the largest value Sheet1!A:A less than
or equal to Sheet2!B1. It assumes Sheet1!A:A is sorted in ascending order, and
if it isn't it becomes confused and could return errors. If it finds a (possibly
approximate) match in Sheet1!A:A, it returns the corresponding value from
Sheet1!B:B.

In the second formula, you're searching a different column, Sheet1!B:B rather
than Sheet1!A:A, and if found, returning the corresponding value in Sheet1!C:C.

Which do you mean to do?