How to use Index?

G

Guest

Does anyone have any suggestions on how to locate a value from a list?
The given list of numbers under column A
1,3,5,7,11,13,17,19,23
If I enter an integer 15 in cell B2, then it will return 17 in cell B1 and
13 in cell B3.
If I enter an integer 13 in cell B2, then it will return 13 in cell B1 and
13 in cell B3, since the list contains the number of 13
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric
 
R

Ron Rosenfeld

Does anyone have any suggestions on how to locate a value from a list?
The given list of numbers under column A
1,3,5,7,11,13,17,19,23
If I enter an integer 15 in cell B2, then it will return 17 in cell B1 and
13 in cell B3.
If I enter an integer 13 in cell B2, then it will return 13 in cell B1 and
13 in cell B3, since the list contains the number of 13
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric
Click to expand...

B1: =INDEX(A1:A9,MATCH(B2,A1:A9,1))
B3: =INDEX(A1:A9,MATCH(B2,A1:A9,1)+1*(COUNTIF(A1:A9,B2)<1))


--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to set the function - countif? 4
How to add conditions to Match function? 2
How to determine the value - 18 Dec? 5
How to valid the date with given condition? 1
How to set the formula? 4
How to use Indirect functions? 3
How to check the value? 2
How to determine the closest value? 7

Top