Excel formula to find a value for a group and range

[haissk]

Hello Experts,

Please help me here. I have a table like below
[TABLE="width: 276"]
<colgroup><col width="69" span="4" style="width:51pt"> </colgroup><tbody>[TR]
[TD="width: 69"]Group[/TD]
[TD="width: 69"]Min[/TD]
[TD="width: 69"]Max[/TD]
[TD="width: 69"]Price[/TD]
[/TR]
[TR]
[TD]G1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]G1[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]G1[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD]G1[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]400[/TD]
[/TR]
[TR]
[TD]G2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]G2[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]130[/TD]
[/TR]
[TR]
[TD]G2[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]140[/TD]
[/TR]
[TR]
[TD]G2[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]150[/TD]
[/TR]
</tbody>[/TABLE]

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