Excel: Can INDEX functions be Variable?


Hi, [FONT=Times New Roman] Hi, 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: =IF(C18="AgeLimit",INDEX(AgeLimit,MATCH(1,(Reseller=C6)*(Level=C12)*(PolicyType=C9),0)),IF(C18="HazardousActivities",INDEX(Hazardous1,MATCH(1,(Reseller=C6)*(Level=C12)*(PolicyType=C9),0)),IF(C18="Valuables",INDEX(Valuables1,MATCH(1,(Reseller=C6)*(Level=C12)*(PolicyType=C9),0))))) 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? [I]Possible Solutions:[/I]

[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] INDEX(INDIRECT(R1),MATCH("Ralph",INDIRECT(R1&"[Name]"),0),MATCH("Sales",INDIRECT(R1&"[HEADERS]"),0))
[I]Also, to delete any spaces so formula can work with multi word entries in a drop down list.[/I]
INDIRECT(SUBSTITUTE($C$15," ",""))
Thanks so much, Mike [/FONT] [/COLOR]


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