if area between and numbe rof bedroom then value

[cjharley1450]

Hi All
I am trying to do the following
If area required is 83, with 3 bedrooms. then the value required is 60000

a B C D E F G H

1 areas bedrooms
2 1 2 3 4 5
3 0 50 35000 40000 50000 60000 65000
4 51 60 35000 40000 50000 60000 65000
5 61 70 45000 45000 50000 60000 65000
6 71 80 50000 50000 50000 60000 65000
7 81 90 55000 60000 60000 60000 65000
8 91 100 65000 60000 65000 60000 65000


area bedrooms value
82 3 60000
how do i do this, completely lost

 

[Eric W]

Assuming I got your data lined up properly, this should work:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1		areas		bedrooms							area	bedrooms	value
2				1	2	3	4	5			82	3	60000
3	1	0	50	35000	40000	50000	60000	65000
4	2	51	60	35000	40000	50000	60000	65000
5	3	61	70	45000	45000	50000	60000	65000
6	4	71	80	50000	50000	50000	60000	65000
7	5	81	90	55000	60000	60000	60000	65000
8	6	91	100	65000	60000	65000	60000	65000
9	7
10	8
11

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula M2 =INDEX(D3:H8,MATCH(K2,B3:B8),MATCH(L2,D2:H2)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[cjharley1450]

assuming i got your data lined up properly, this should work:

	a	b	c	d	e	f	g	h	i	j	k	l	m
1		areas		bedrooms							area	bedrooms	value
2				1	2	3	4	5			82	3	60000
3	1	0	50	35000	40000	50000	60000	65000
4	2	51	60	35000	40000	50000	60000	65000
5	3	61	70	45000	45000	50000	60000	65000
6	4	71	80	50000	50000	50000	60000	65000
7	5	81	90	55000	60000	60000	60000	65000
8	6	91	100	65000	60000	65000	60000	65000
9	7
10	8
11

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

sheet2​

worksheet formulas cell formula m2 =index(d3:h8,match(k2,b3:b8),match(l2,d2:h2)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

thanks, worked a treat buddy