Excel find a value <>2#'s &get a value beside [Solved/Closed]

Report
-
 g -
Hi All,

I want to enter a number in cell A1 (Sheet 1) and have a formula which gets excel to go to sheet 2 column A finds the two numbers the entered value falls between then returns the number adjacent in column B (sheet 2) to cell A2 (Sheet 1).

E.g. I enter 847 into cell A1 (Sheet 1). Therefore a value of 14 appears in A2 (Sheet 1)

Column A (Sheet2) ......Column B (Sheet 2)
840 ............................13
841 ............................14
890 .............................14
891 ..............................15
940 .............................15
941 .............................16
990 .............................16
991 .............................17
1040 ........................... 17
1041 .............................18
1090 .............................18

Any help would be great. Thanks.

g

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
Try this

=IF(ISERROR(VLOOKUP(A1,Sheet2!A:B,2,TRUE), "", VLOOKUP(A1,Sheet2!A:B,2,TRUE))


or this

=IF(ISERROR(VLOOKUP(A1,Sheet2!A:B,2,TRUE)),"",VLOOKUP(A1,Sheet2!A:B,2,TRUE))
which is same but the later handles any error too
thankyou. Much appreciated!

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!