Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

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
See more

2 replies

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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!