Finding Values in a Table

[markhib]

Hi There,

Please help!

i have a table set out as follows,

	A	B	C	D	E
1	Age of Debt	Business	Person	Govt	Other
2	Current	0%	0%	5%	0%
3	Up to 30 days overdue	10%	5%	5%	0%
4	31 - 60 days overdue	20%	5%	20%	0%
5	61 - 90 days overdue	30%	15%	20%	0%
6	91 - 120 days overdue	40%	15%	20%	0%
7	121 - 150 days overdue	50%	15%	20%	0%
8	151 - 180 days overdue	60%	45%	20%	50%

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

In a seperate sheet i have a bunch of outstanding transactions and want to apply the correct rate dependant on wether the entity is a Business, Person, Gov or Other and the age of the Debt is Current, 30 days etc etc.

I thought about if statements but too many possibilities, Ive tried concatenating age and entity and creating a list to do a vlook up but am sure there is a better way..

Many Thanks

MArk

 

[Yongle]

one way

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	overdue	Business	Person	Govt	Other			overdue	who ?		formula in J2
2​	180​	60%​	45%​	20%​	50%​			125​	Business	50%​	=INDEX($B$2:$E$8,MATCH(H2,$A$2:$A$8,-1),MATCH(I2,$B$1:$E$1,0))
3​	150​	50%​	15%​	20%​	0%​
4​	120​	40%​	15%​	20%​	0%​
5​	90​	30%​	15%​	20%​	0%​
6​	60​	20%​	5%​	20%​	0%​
7​	30​	10%​	5%​	5%​	0%​
8​	0​	0%​	0%​	5%​	0%​

Sheet: Sheet2

 

[Yongle]

you probably need a catch-all row

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	overdue	Business	Person	Govt	Other			overdue	who ?		formula in J2 copied down
2​	9999​	666%​	666%​	666%​	50%​			125​	Business	50%​	=INDEX($B$2:$E$9,MATCH(H2,$A$2:$A$9,-1),MATCH(I2,$B$1:$E$1,0))
3​	180​	60%​	45%​	20%​	50%​			500​	Person	666%​
4​	150​	50%​	15%​	20%​	0%​			25%​	Govt	5%​
5​	120​	40%​	15%​	20%​	0%​
6​	90​	30%​	15%​	20%​	0%​
7​	60​	20%​	5%​	20%​	0%​
8​	30​	10%​	5%​	5%​	0%​
9​	0​	0%​	0%​	5%​	0%​

Sheet: Sheet2