Excel formula to find a value for a group and range

[haissk]

Hello Experts,

Please help me here. I have a table like below

Group	Min	Max	Price
G1	0	10	100
G1	11	20	200
G1	21	30	300
G1	31	40	400
G2	0	10	120
G2	11	20	130
G2	21	30	140
G2	31	40	150

<colgroup><col width="69" span="4" style="width:51pt"> </colgroup><tbody>
</tbody>

when if enter Group and Range value it should return the price
Example G1 - 12 should return 200
G2 - 39 should return 150

Can you please help me with excel formula

 

[etaf]

where do you enter the value ie 12 or 39

the following post may help
choice of formulas

 

[etaf]

incorrect format of jeanie

the description is your G codes
the Product ID would have your costs in

and item in tge table your costs

i will change to your details - if this is what you are after and where you enter the info cells ?

 

[etaf]

Excel Workbook
	A	B	C	D	E
1	Description:	Product 2
2	Cost:	30
3	Item #:	100004		IFERROR(LOOKUP(1E+307,1/((B1=A11:A16)*(B2>=B11:B16)*(B2
4	OR	100004		LOOKUP(B2,OFFSET(B10:D16,MATCH(B1,A11:A16,0),,COUNTIF(A11:A16,B1)))
5	OR	100004		LOOKUP(2,1/((A11:A16=B1)*(B11:B16
6
7
8
9
10	Description	Min Cost	Max Cost	Item #
11	Product 1	1	25	100001
12	Product 1	26	50	100005
13	Product 1	51	75	100030
14	Product 2	1	25	100033
15	Product 2	26	50	100004
16	Product 2	51	75	100009
Sheet1

 

[admiral100]

Hi,

Let's say E1 contain group G1 and F1 contain 12

G1 =INDEX(D2:D9,MATCH(1,IF(A2:A9=E1,MATCH(C2:C9,F1,1)),0)) Ctrl+Shift+Enter Not just Enter

 

[haissk]

Thank you all. It helped me to solve my problem.

 

[meytithveasna]

Try this
Lookup(2,1/((A$2:A$9=E1)*(F1>=B$2:B$9)*(F1<=C$2:C$9)),D$2:D9)
E1 house G1
F1 house 12