• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index Match formula with column and row references in another cell

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.
 
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:
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),
  columnIndex, XMATCH(currency, Table1[#Headers]),
  INDEX(selectedtable, recordNum, columnIndex))
in modern Excel or
Code:
= INDEX( 
   CHOOSE(MATCH(Select,TableList,0), Table1, Table2), 
   recordNum, 
   MATCH(currency, Table1[#Headers],0))
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.
 
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))
 
Back
Top