[FONT=Times New Roman]
I’ve had many happy years enjoying VLOOKUP, but now I’vemoved on to a deeper relationship with INDEX MATCH ;o)
I am using INDEX MATCH to return values from a table with 54columns.
There are 3 criteria to MATCH -
MATCH expressions are variable – they are taken from dependentdrop down lists in cells C6, C12 and C9
An example of my inelegant but working formula is:
I would like to avoid using 54 nested IF Statements toindicate which column the INDEX function should return values from.
[I]My questions are:[/I]
Can the INDEX function be variable also?
Can anyone show me how to combine INDEX MATCH with INDIRECTSUBSTITUTE to make the INDEX function variable?
Thanks so much,
| [COLOR=black]I found this on another Mr Excel post – the closest solution I have found in my research.
[I]To make INDEX function variable:[/I]
[I]Also, to delete any spaces so formula can work with multi word entries in a drop down list.[/I]
| INDIRECT(SUBSTITUTE($C$15," ",""))
This question generated 11 answers. To proceed to the answers, click here.
This thread is current as of January 15, 2015.
For more resources for Microsoft Excel