Indirect & Match

[Ebes1099]

I'm using an Indirect function right now to look up a value from another worksheet where the other worksheet name is pulled in from a cell on the same worksheet the formula is on. But right now I have the cell to look at hard coded into the formula. I'd rather have it be a lookup in case the rows get changed on the lookup worksheet.

I'm guessing there's a way to do this but it seems to get complicated.

Here is my current formula,

=INDIRECT("'" & $F$2 & "'!" & "G16")*$E6

In cell F2 is the name of the worksheet I want to lookup in. G16 is the cell I want to grab the value from. The value I'm looking up is a $ value for a specific code. The code number is listed in the lookup worksheet and in the current worksheet, so I want to change the formula where G16 is to say something like, Match(A6,F2!A1:A300), where A6 is the code in this sheet I want to find in the range A1:A300 of the lookup sheet name which is in Cell F2.

Is there a way I can do this? Thanks.

 

[SkipVought]

hi,

INDIRECT resolves references. Does not evaluate formulas.

What is the business case for this approch?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

something like this works...
[tt]
=MATCH(A6,INDIRECT(F2&"!A1:A300"))
[/tt]
where A6 contains a lookup value that can be found in A1:A300 on the sheet name contained in F2.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ebes1099]

What I am doing is looking up a price for a certain code. There is a main sheet with all the codes, then there are separate lists that only have some codes that have special arrangements.

It's possible some codes get added to the main list year to year so the rows can shift down. If I use the indirect formula I typed in the first post, the lookup cell is hardcoded in so it won't refelct the change in row. I would like to be able to just use a lookup so if I add codes and the row shifts down the special arrangement sheets will still work fine.

 

[SkipVought]

FYI, all that MATCH returns is the relative offset of the lookup value in the lookup range.

You would need to combine MATCH with INDEX to pick off the data in a different column on the match row.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ebes1099]

Yea, Skip, I figured that. Thanks for the help.

It looks like this formula will work for me.

=INDEX(INDIRECT("'"&$F$2&"'!$G$10:$G$700"),MATCH(A6,INDIRECT("'"&$F$2&"'!$A$10:$A$700")))*$E6