Can a "LOOKUP" function be used this way?

[gorkyness]

Sheet A
----------
A1 (A) - B1 (6)
A2 (D) - B2 (2)
A3 (C) - B3 (3)
A4 (E) - B4 (1)
A5 (B) - B5 (5)

Sheet B
----------
A1 (C) - B1 (* formula)
A2 (E) – B2 (formula)
etc…

* The formula should search A1:A5 on Sheet A for the value of A1 on
Sheet B, then produce the value of the adjacent cell in column B of
Sheet A.

In this case, the value of A1 on Sheet B (“C”) appears only in A3 on
Sheet A, so the value of B1 on Sheet B will be the value of B3 on Sheet
A (in this case “3”).

 

[A.W.J. Ales]

Gorkyness,

Use the VLOOKUP function.

In cell A1 on sheetB

=VLOOKUP(A1,'Sheet 1'!$A$1:$B$5,2,0)

Copy down to cell B1 on sheet B

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Frank Kabel]

Hi
use VLOOKUP. enter the following in B1 on sheet B:
=VLOOKUP(A1,'Sheet A'!$A$1:$B$99,2,0)
copy down

HTH
Frank

 

[keyur]

hey

try placing this in B1
VLOOKUP(A1,Sheet1!$A$2:$B$5,2,0) and then fill down.

the parameters are explained in excel help.

 

[Guest]

Believe I'm trying to do the same thing - the Array in the lookup has to be sorted. I found that Article 181212 in the Microsoft Knowledge Base "Performing a Lookup with Unsorted Data in Excel" may help. I have not been able to get it to work yet though.

It uses a combination of INDEX and MATCH functions

 

[Frank Kabel]

Hi Fabien
you can use VLOOKUP or HLOOKUP on unsorted ranges. You have to provide
the 4th parameter for both functions (FALSE).
Only the function LOOKUP won't work on unsorted array.

Frank