Compare and Show

[WjmExcelHelp]

Could someone please help me with a formula for excel?

What I need to do is compare cell A1 with column B1:C4 and when there is a match or the figure is between the numbers, it should show the number in cell C, but this will be on another sheet called sheet 2

Sheet 1

	A	B	C	D
1	R165 000.00	R0.00	R100 000.00	15
2		R100 000.01	R200 000.00	10
3		R200 000.01	R300 000.00	7
4

<tbody>
</tbody>

Sheet 2
If Sheet 1, A1 falls between B2 and C2 then Sheet 2, A1 must show Sheet D2

	A	B	C	D
1	10
2
3
4

<tbody>
</tbody>

Please help me with a formula for sheet 2 A1

 

[WjmExcelHelp]

Could someone please help with a formula to get this answer? I looked at the other comments and searces but I don't find any code that will work.

 

[Fluff]

Try
=INDEX(Sheet1!D2:D4,MATCH(Sheet1!A2,Sheet1!B2:B4,1))

 

[WjmExcelHelp]

The formula works 100% when all the data is on one sheet, as soon as I refer Sheet!1A2 to another sheet it gives me #N/A error.

 

[WjmExcelHelp]

Ok thanks, I have found my mistake. Thank you so much for your help. You are the best.

 

[Fluff]

You're welcome & thanks for the feedback