# Index Match formula with column and row references in another cell

#### westend9876

##### Member
Here is my current working formula
=INDEX(AUDUSD!AF31:AUDUSD!AM31,0,MATCH(E6,AUDUSD!AF1:AUDUSD!AM1,0))

However, I need to reproduce a formula that will produce the same result with the cell references broken apart in other cells as follows:
A8 - AUDUSD
B8 - 1
C8 - 31
D8 - AF
E8 - AM
F8 - The final formula to produce the result.

#### vletm

##### Excel Ninja
westend9876
Could You have an idea to send an Excel-sample-file based Your data?
It would improve Your possibilities to get reproduced formula.

#### pecoflyer

##### Active Member
Have a look at the INDIRECT function ( but that will be a challenge to set up)
Why do you need to make this so complicated?

Last edited:

#### Peter Bartholomew

##### Well-Known Member
I really do not like synthesising direct cell references; but then, I wouldn't, since I am the one person that believes the A1 cell notation to have been a rubbish idea from the outset! I would list the available tables/ranges by name and look up the one you want using CHOOSE. Also look up the currency name from the table headings to return a column index. The formula is then
Code:
``````= LET(
option, XMATCH(Select, TableList),
selectedtable, CHOOSE( option, Table1, Table2),
INDEX(selectedtable, recordNum, columnIndex))``````
in modern Excel or
Code:
``````= INDEX(
CHOOSE(MATCH(Select,TableList,0), Table1, Table2),
recordNum,
in legacy versions. My reason for preferring formulas that reference structured objects on the sheet rather than cell references is that the formulas are readable without the need to decode cell references to determine what they contain.

#### westend9876

##### Member
Thank you all. I've come up with this formula using Indirect that works for my needs.

=INDEX(INDIRECT("'"&A8&"'!"&D8&C8&":'"&A8&"'!"&E8&C8),0,MATCH(E6,INDIRECT("'"&A8&"'!"&D8&B8&":'"&A8&"'!"&E8&B8),0))

#### westend9876

##### Member
Have a look at the INDIRECT function ( but that will be a challenge to set up)
Why do you need to make this so complicated?
Yes. Indirect is indeed complicated but I managed to figure it out.