Reference formulas...

FabianSparkle

New Member
Joined
Jul 21, 2002
Messages
23
Does anyone know whether it is possible to use one of the 'lookup' or other reference functions when the 'Lookup_Value' is text and can be different (ie: the text can differ between sheets)?

Thanks for your assistance in advance.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
VLOOKUP / HLOOKUP / MATCH can all lookup text BUT what do you mean when you say the text can differ between sheets etc... care to elaborate a little more?
 

FabianSparkle

New Member
Joined
Jul 21, 2002
Messages
23
Elaborate, yeah - I have a workbook that contains a number of worksheets within it. On each sheet there is an IF formula that returns text depending on the result. Further, that result may appear in any cell within a column range (which prevents use of a direct link and the need for a lookup function). Anyway, there is a summary sheet that I use to display this information. Using the LOOKUP formula requires a specific 'Lookup_Value' (ie: the result of the IF formula) but this can change. Hence my problem.

I hope this makes sense...
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Do you know which sheet you will want to look at in your vlookup or do you need to search through all sheets until you find a match?
 

FabianSparkle

New Member
Joined
Jul 21, 2002
Messages
23

ADVERTISEMENT

I know which sheets contain the IF formula text results.

Thanks again for your assistance.
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
I guess what I am getting at is if you know what sheet it's on and you know the text you want to look up then I guess you want to find an adjacent cell?

So if say the text is "LASW10" and you know the if formula is on sheet1 in ColA (you want what's in column B)

=VLOOKUP("LASW10",Sheet1!A:A,2,FALSE)

Or if you would prefer to be able to enter the text in a cell and then lookup from that then say A1 = "LASW10"

=VLOOKUP(A1,Sheet1!A:A,2,FALSE)

I get the feeling I am probably missing the point so if this isn't what you're after please let us know.

Luke
 

FabianSparkle

New Member
Joined
Jul 21, 2002
Messages
23

ADVERTISEMENT

Hi Luke -

Thanks for the reply. Using an example is probably the easiest way of illustrating it.

So using your example, the problem I have is that the Lookup_Value (ie: 'Lasw10') has 2 possible results. So my Lookup_Value could be either say 'Lasw10' or 'Lasw11' (as determined by the IF formula in the various worksheets). What I need is a formula that will display either the the 'Lasw10' or 'Lasw11' depending on which ever it is.

Hopefully this makes things clearer.

Cheers
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
OK so you need to use IFs with the VLOOKUPs

Say in A1 I have LASW10

=IF(ISERROR(VLOOKUP(A1,Sheet1!A:A,1,FALSE)),VLOOKUP(LEFT(A1,4)&RIGHT(a1,2)+1,Sheet1!A:A,1,FALSE),VLOOKUP(A1,Sheet1!A:A,1,FALSE)

Does that do what you want?
 

FabianSparkle

New Member
Joined
Jul 21, 2002
Messages
23
Luke -

Thanks for your response but I'm a little confused by your formula. What is the significance of the 'Left' & 'Right' functions?

I have now got it working using a hybrid of your formula. I'm keen to understand your formula however.

Thanks again for your help Luke.

Cheers
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
sorry I was just continuing on from your example of LASW10 & LASW11

=left(a1,4) = LASW (4 characters from left in A1)
=right(A1,2)+1 = 11 (2 characters from right in A1 (10) + 1)

So if LASW10 generated an error then lookup LASW11 (where LASW11 was generated by combining the left & right formulas)

Glad you got it working.
 

Forum statistics

Threads
1,143,923
Messages
5,721,559
Members
422,370
Latest member
A Nonomus

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