How to return a Match result from a column # (formula generated) in a named range

Hi all,

I am going slightly crazy trying to input a formula and would love some help!

I want to create a formula that is essentially an Match formula pulling its result from a named range, but the column number is formula generated itself in a helper column by an array formula. I can't for the life of me work it out.

I'll try and explain this as best I can:
I would like my results to be in column H. For this one particular formula (which I intend to copy and paste down column H) I need my result to be in H3.

In cell G3, I have a 'helper' formula. This is an array formula as follows:
{=MATCH(C3&A$1&"Lookup value for current price",MarkdownFormat&MarkdownGame&MarkdownCriteria,0)} and this tells me that the column which my result for cell H3 will come from, is column 2 of named range Markdowns. (just for further info, cell G4 tells me column 4, cell G5, a different column # etc which is why I need the match/index formulas in column H to be dynamic and not a fixed specified column number to look up against).

My formula in cell H3 therefore needs to be a match formula: match("reference to find","col # in cell G3 on named range Markdowns",0)

Can anyone tell me if this is possible to do? I currently have to specify the numbers manually, but this is tedious because I am copying this 'template' into many other locations on the same worksheet based on many look up references, and in each template I have to manually change the data range. It leaves it open to human error too if I forget. I also have all the templates treated as tables so I can filter them all the same with 1 click. This however causes problems when I manually change the data range, unless I convert them all to data first, then re convert them all back to tables after adjustments.

My current formula in cell H3 based on my manual input is:
=IF(ISNA(MATCH(B12&" "&I12&" pp",Tracker!$EZ$1:$EZ$550,0)),0,MATCH(B12&" "&I12&" pp",Tracker!$EZ$1:$EZ$550,0))

In a nutshell:
"Tracker!$EZ$1:$EZ$550" is the element of the formula that I need to replace with: look at column number X (pulled through from column G) at named range "Markdowns".if anyone can offer a solution, I'd be extremely grateful!

Kind of hard to follow, but perhaps you need INDIRECT?

e.g.


=IF(ISNA(MATCH(B12&" "&I12&" pp",INDIRECT($G$3),0)),0,MATCH(B12&" "&I12&" pp",INDIRECT($G$3),0))

G3 contains the range to look up brought back by your other formula.

Hmmm... so Markdowns is a named range, and G3 is column within that range you want to reference?

If so, maybe not indirect, may index...

=IF(ISNA(MATCH(B12&" "&I12&" pp",INDEX(Markdowns,0,$G$3),0)),0,MATCH(B12&" "&I12&" pp",INDEX(Markdowns,0,$G$3),0))