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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

WjmExcelHelp

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,264
Office Version
  1. 365
Platform
  1. Windows
Try
=INDEX(Sheet1!D2:D4,MATCH(Sheet1!A2,Sheet1!B2:B4,1))
 

WjmExcelHelp

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,264
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,108,911
Messages
5,525,588
Members
409,652
Latest member
strangelyangely

This Week's Hot Topics

Top