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.

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

<tbody>
</tbody>

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!