Compare and Show

WjmExcelHelp

New Member
Joined
Mar 25, 2019
Messages
4
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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.
 
Upvote 0
Try
=INDEX(Sheet1!D2:D4,MATCH(Sheet1!A2,Sheet1!B2:B4,1))
 
Upvote 0
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.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top