MATCH & INDEX (what am I missing)

This is going to be complicated to explain but I'll try my best. I've attached my project to help you understand.


I have three sheets TAKEOFF, PCFB & PCFB1.

In cell G6, I have this formula: =INDEX(PCFG!$U$6:$AA$37,MATCH(C6,PCFG!$U$6:$U$37,0),MATCH(D6,PCFG!$U$6:$AA$6,0)) in cell G6 of the TAKEOFF sheet.

I'm using this formula to return the intersecting cell on the PCFG sheet base on what I have in cell C6 & D6 of the TAKEOFF sheet.

In its current state, I have "PCFG" (a sheet name) baked into the formula. I need that part to be dynamic, based on which sheet name I put in cell B6.

The formula currently doesn't use cell B6, but I need to be able to specify a sheet name "PCFG" or "PCFG1" in B6 and have the rest of the formula pull the data from that sheet.

Can this be done?

This could be done by using the INDIRECT function.
But this will make formulas cumbersome and eventually slow calculations down as INDIRECT is a volatile function

As the only difference between PCFG sheets is the discount percentage why not use L6:R37 form PCFG sheet as base prize and enter the discount in B6 ?
Adapt your formula with the necessary ranges and multiply it by B6

airpuncher said:

Can this be done?

Click to expand...

See if you get this formula with the INDIRECT function solves the problem, such as a colleague @Pecoflyer suggested
Put this formula in G6

@navic
Thanks for quoting me, but I think the use of INDIRECT is not the most efficient technique in this case

Pecoflyer said:

This could be done by using the INDIRECT function.
But this will make formulas cumbersome and eventually slow calculations down as INDIRECT is a volatile function

As the only difference between PCFG sheets is the discount percentage why not use L6:R37 form PCFG sheet as base prize and enter the discount in B6 ?
Adapt your formula with the necessary ranges and multiply it by B6

Click to expand...

Thanks, Pecoflyer. You're right, in the example, the percentage is the only difference between the PCFG sheets. This won't be the case once I flesh this thing out. Thanks for the suggestion though.

What do you mean by "INDIRECT is a volatile function"?

Thanks.