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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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?
 
Upvote 0
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...
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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
Back
Top