lookup / index formula

[hyd1956]

Hi all,

Can anyone help with a formula which looks up the percentage value in column A sheet 2 against columns A and B in Sheet 1, then looks up column b sheet 2 against columns c and d sheet 1 and then displays the corresponding figure from column E?

Book1
	A	B
1	Percentage	Total
2	20.00%	1
3	30.00%	2500
4	40.00%	2050
5	50.00%	90000
6	60.00%	100000
7	70.00%	3000
8	80.00%	750000
9	90.00%	2000
10	100.00%	1000
Sheet2

Book1
	A	B	C	D	E	F
1	Min Percentage	Max Percentage	Min Number	Max Number	Col1	Col2
2	20%	49%	1	5000	1	20.00
3			50001	200000	2	30.00
4			2000001	99999999	3	40.00
5	50%	80%	1	5000	4	50.00
6			50001	200000	5	60.00
7			2000001	99999999	6	70.00
8	81%	100%	1	5000	7	80.00
9			50001	200000	8	90.00
10			2000001	99999999	9	100.00
Sheet1

Any help would be great, I tried vlookups with name ranges but I couldn't get it to work. Thought it would be something like =IFERROR(IF(AND(A2>MInP,A2<MaxP),LOOKUP(2,1/((B2>=MinN)*(J2<=MaxN)),colone),""),"") but I could be miles off!

MinP =sheet1, column a
MaxP =sheet1, column b
MinN =sheet1, column c
MaxN =sheet1, column d
colone =sheet1, column e

Thank you

 

[mamady]

Hi Dear,

I have difficulties in understanding the relationship between the values in sheet 1 and 2, and what formula was used in Column A in sheet 1

let us work it in this way; if the value is >20% and <49%, and as a number, it is >1 and <5000, what value should be retrieved from sheet 2 and why?

Best Regards
m.yusuf

 

[Peter_SSs]

Does this do what you want?

hyd1956.xlsm
	A	B	C
1	Percentage	Total	Result
2	20.00%	1	1
3	30.00%	2500	1
4	40.00%	2050	1
5	50.00%	90000	5
6	60.00%	100000	5
7	70.00%	3000	4
8	80.00%	750000	5
9	90.00%	2000	7
10	100.00%	1000	7
Sheet2

Cell Formulas
Range	Formula
C2:C10	C2	=INDEX(Sheet1!E:E,MATCH(A2,Sheet1!A:A)+MATCH(B2,Sheet1!C$2:C$4)-1)