Can INDEX functions be Variable?

[dangerousmouse]

Hi,<o></o>
<o> </o>



<o>
Hi,<o></o>

<o> </o>

I’ve had many happy years enjoying VLOOKUP, but now I’vemoved on to a deeper relationship with INDEX MATCH ;o)<o></o>

<o> </o>

<o> </o>

I am using INDEX MATCH to return values from a table with 54columns.<o></o>

<o> </o>

There are 3 criteria to MATCH -<o></o>

MATCH expressions are variable – they are taken from dependentdrop down lists in cells C6, C12 and C9<o></o>

<o> </o>
An example of my inelegant but working formula is:<o></o>

=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)))))<o></o>


I would like to avoid using 54 nested IF Statements toindicate which column the INDEX function should return values from.<o></o>

<o> </o>
My questions are:<o></o>

Can the INDEX function be variable also?<o></o>

Can anyone show me how to combine INDEX MATCH with INDIRECTSUBSTITUTE to make the INDEX function variable?<o></o>

<o> </o>
Possible Solutions:<o></o>

[TABLE="class: MsoNormalTable, width: 469"]
<tbody>[TR]
[TD="width: 626, bgcolor: transparent"] I found this on another Mr Excel post – the closest solution I have found in my research.<o></o>
<o> </o>
To make INDEX function variable:<o></o>
INDEX(INDIRECT(R1),MATCH("Ralph",INDIRECT(R1&"[Name]"),0),MATCH("Sales",INDIRECT(R1&"[HEADERS]"),0))<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 626, bgcolor: transparent"] <o> </o>
Also, to delete any spaces so formula can work with multi word entries in a drop down list.<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 626, bgcolor: transparent"] INDIRECT(SUBSTITUTE($C$15," ",""))<o></o>
[/TD]
[/TR]
</tbody>[/TABLE]


Thanks so much,<o></o>

<o> </o>

Mike<o></o>

</o>

 

[Aladin Akyurek]

Hello Again,

I solved this by looking at each parenthesis and inserting an extra one alongside.
This eventually gave me the answer based on Aladin's awesome combo.

{=INDEX(INDIRECT(SUBSTITUTE($C$18," ","")),MATCH(1,(Reseller=C6)*(Level=C12)*(PolicyType=C9),0))}

Thank you Everyone for your Direction and Guidance!

Mike

You are welcome.