If Lookup

Dave Ashcroft

New Member
Joined
May 24, 2008
Messages
24
Can anyone help!!:eek:
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.:eek:
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Can anyone help!!:eek:
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.:eek:

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

New Member
Joined
May 24, 2008
Messages
24
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,691
Messages
5,597,562
Members
414,156
Latest member
WDMix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top