If Lookup

[Dave Ashcroft]

Can anyone help!!
I have a worksheet cell populated by a Lookup as follows;
=LOOKUP(D4,pfo!A1:A10,pfo!B1:B10),.
I need some way to look at cell C4:C100 and based on it's contents ie "pfo" perform the Lookup as above, if C4 is "pso" then use;
=LOOKUP(D4,pso!A1:A10,pso!B1:B10).
I know that =IF(I4="pfo",LOOKUP(D4,pfo!A1:A10,pfo!B1:B10) works but how do I create the looping to check for the second condition.

Thanks in advance.

 

[Aladin Akyurek]

Can anyone help!!
I have a worksheet cell populated by a Lookup as follows;
=LOOKUP(D4,pfo!A1:A10,pfo!B1:B10),.
I need some way to look at cell C4:C100 and based on it's contents ie "pfo" perform the Lookup as above, if C4 is "pso" then use;
=LOOKUP(D4,pso!A1:A10,pso!B1:B10).
I know that =IF(I4="pfo",LOOKUP(D4,pfo!A1:A10,pfo!B1:B10) works but how do I create the looping to check for the second condition.

Thanks in advance.

LOOKUP would require that A1:A10 is sorted in ascending order. If A1:A10 is text, the LOOKUP formula needs to be modified. That said:

=LOOKUP(D4,INDIRECT("'"&C4&"'!A1:A10"),INDIRECT("'"&C4&"'!B1:B10"))

If A1:A10 is unsorted...

=VLOOKUP(D4,INDIRECT("'"&C4&"'!A1:B10"),2,0)

Is one of these what you are after?

 

[Dave Ashcroft]

Thanks for the quick response.
The numeric values in A1:A10 are in assending order and the code works perfectly.
Thanks very much for your time.

 

[Aladin Akyurek]

Thanks for the quick response.
The numeric values in A1:A10 are in assending order and the code works perfectly.
Thanks very much for your time.

You are welcome.

You can also invoke VLOOKUP for the sorted case in case calling INDIRECT once could matter...

=VLOOKUP(D4,INDIRECT("'"&C4&"'!A1:B10"),2,1)