Previous Topic Next Topic

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

Answer 1 / 2
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
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
Answer 2 / 2

thankyou. Much appreciated!
Ask a question