Index & Match Multiple Criteria

[Legskeattch]

I apologise in advance if this is a confusing post!

At the moment I have a table of different investments in column A and in the columns adjacent I have the percentage allocation to each risk profile. The simplified table below should explain better.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Investment[/TD]
[TD]Low Risk[/TD]
[TD]Cautious Income[/TD]
[TD]Cautious Growth[/TD]
[TD]Moderate Income[/TD]
[TD]Moderate Growth[/TD]
[TD]High Growth[/TD]
[TD]Aggressive Growth[/TD]
[/TR]
[TR]
[TD][TABLE="width: 382"]
<tbody>[TR]
[TD]Royal London Corporate Bond M[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]10%[/TD]
[TD]9%[/TD]
[TD]3%[/TD]
[TD]1%[/TD]
[TD]2%[/TD]
[TD]4%[/TD]
[TD]7%[/TD]
[/TR]
[TR]
[TD][TABLE="width: 382"]
<tbody>[TR]
[TD]Rathbone Ethical Bond I[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]15%[/TD]
[TD]13%[/TD]
[TD]0%[/TD]
[TD]3%[/TD]
[TD]0%[/TD]
[TD]4%[/TD]
[TD]6%[/TD]
[/TR]
[TR]
[TD][TABLE="width: 382"]
<tbody>[TR]
[TD]Kames Investment Grade Bond B[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]0%[/TD]
[TD]12%[/TD]
[TD]7%[/TD]
[TD]2%[/TD]
[TD]4%[/TD]
[TD]0%[/TD]
[TD]12%[/TD]
[/TR]
[TR]
[TD][TABLE="width: 382"]
<tbody>[TR]
[TD]Aviva Investments Higher Income Plus 2[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]7%[/TD]
[TD]7%[/TD]
[TD]6%[/TD]
[TD]3%[/TD]
[TD]8%[/TD]
[TD]9%[/TD]
[TD]2%[/TD]
[/TR]
</tbody>[/TABLE]


At the top of my spread sheet I have a drop down with the risk profile of the client (low risk, cautious income etc) and I want to then be able to pre populate a table with the investments that are associated to that risk profile.

For example:

If I select Low Risk in the drop down, I expect Royal London 10%, Rathbone 15%, Aviva Investments 7% and for the Kames investment to not show on in the table.

Then, if I change the risk profile to Cautious Growth, I want the table to show Royal London 3%, Kames 7% and Aviva 6%.

Any help would be greatly appreciated.




Luke

 

[Legskeattch]

I don't know whether it might be more efficient to use a macro?

 

[CatOnASpreadsheet]

Hi Luke,

It can be done with INDEX / MATCH but I'd probably go with VLOOKUP / MATCH instead. The following formula assumes your example table starts in cell A1 and the drop down is in cell Z1:

=VLOOKUP(A2,A:H,MATCH($Z$1,$B$1:$H$1,0)+1,0)

Now this will not return blanks instead of zeros but you can achieve that by adding in an IF:

=IF(VLOOKUP(A2,A:H,MATCH($Z$1,$B$1:$H$1,0)+1,0)=0,"",VLOOKUP(A2,A:H,MATCH($Z$1,$B$1:$H$1,0)+1,0))

I hope this helps.

Cheers,
Catalin

 

[BarryL]

Is it my understanding that you want the table to update based on the selection of a drop down (be it data validation or some form of user form?

 

[Legskeattch]

Is it my understanding that you want the table to update based on the selection of a drop down (be it data validation or some form of user form?

Yes, that's the intention

 

[Legskeattch]

Hi Luke,

It can be done with INDEX / MATCH but I'd probably go with VLOOKUP / MATCH instead. The following formula assumes your example table starts in cell A1 and the drop down is in cell Z1:

=VLOOKUP(A2,A:H,MATCH($Z$1,$B$1:$H$1,0)+1,0)

Now this will not return blanks instead of zeros but you can achieve that by adding in an IF:

=IF(VLOOKUP(A2,A:H,MATCH($Z$1,$B$1:$H$1,0)+1,0)=0,"",VLOOKUP(A2,A:H,MATCH($Z$1,$B$1:$H$1,0)+1,0))

I hope this helps.

Cheers,
Catalin

Thanks very much, will give this a try now!