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]

Unfinished/incomplete formulas do not tell much about problems they purport to solve.

Care to post a scaled-down sample along with the actual results you want to see?

 

[ukmikeb]

Hi

Index has an Areas option which you could use like :-

=INDEX((AgeLimit,Hazardous1,Valuables1),MATCH(1,(Reseller=C6)*(Level=C12)*(PolicyType=C9),0),,MATCH(C18,{"AgeLimit","HazardousActivities","Valuables"},0))

hth

 

[dangerousmouse]

Mike to ukmikeb

That's it.

I have read a lot of tutorials about how to understand and use INDEX.
Not one mentioned an Areas Option.

Genius.

Thanks!

 

[Aladin Akyurek]

How about those 54 "areas"?

 

[dangerousmouse]

How about those 54 "areas"?

What do you mean?

That there is a limit?

 

[dangerousmouse]

...or can I construct a Lookup Table and enter that table as an INDEX Area Option?

 

[Aladin Akyurek]

...or can I construct a Lookup Table and enter that table as an INDEX Area Option?

INDEX(INDIRECT(C18),MATCH-expression)

might be easier on you if C18 houses a regular definition name and there is a correlation between matching and area.

 

[dangerousmouse]

INDEX(INDIRECT(C18),MATCH-expression)

might be easier on you if C18 houses a regular definition name and there is a correlation between matching and area.

Yes Aladin, that works well with Named Ranges that match.

I now need to remove any spaces from the entries in C18 to match the Named Range
eg: C18 = Hazardous Activities Named Range = HazardousActivities
or
eg: C18 = Age Limit Named Range = AgeLimit

I tried your excellent method like this:

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

... entered with shift + control + enter - it being an array formula

An error is returned - The formula is missing a parenthesis.

So close now - can you help me with this one last hurdle?

Thanks for your kind attention so far

Mike

 

[dangerousmouse]

Yes Aladin, that works well with Named Ranges that match.

I now need to remove any spaces from the entries in C18 to match the Named Range
eg: C18 = Hazardous Activities Named Range = HazardousActivities
or
eg: C18 = Age Limit Named Range = AgeLimit

I tried your excellent method like this:

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

... entered with shift + control + enter - it being an array formula

An error is returned - The formula is missing a parenthesis.

So close now - can you help me with this one last hurdle?

Thanks for your kind attention so far

Mike

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