Excel find a value <>2#'s &get a value beside

Solved/Closed
g - Apr 24, 2010 at 08:53 AM
 g - Apr 28, 2010 at 04:35 AM
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

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Apr 24, 2010 at 12:09 PM
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
0
thankyou. Much appreciated!
0