get value from column

[arctushar]

Hi
I have a column and values in the column (A) 1,2,3,4,6,8,12,16,18,25,39,87 in rows.
Now I need that
in Cell B1 value will be input cell and Cell B2 will be output cell
I required this
if B1 is 1 then B2 is 1
if B1 is 3 then B2 is 3
if B1 is 13 then B2 is 16
NB: in Column A values are in lowest to highest order.
So please help me what will be the formula of B2.
I dont want to use VB

[benishiryo]

hi arctushar. if i understood correctly, it should return the same value if it finds an exact match? otherwise, return the next highest value? no match for 13, so it's between 12 & 16. pick 16 because it's higher? if so, try:
=INDEX(A1:A12,MATCH(TRUE,INDEX((A1:A12>=B1),),0))

[Tony Valko]

So, if there is not an exact match then you want the next higher value?

Try this array formula**:

=IF(B1>A12,"N/A",MIN(IF(A1:A12>=B1,A1:A12)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[arctushar]

Thnaks benishiryo very much
Would u plz explain how its working ?
specially what is ),),0)) at last

[FDibbins]

Try this in B2...
=INDEX($A$1:$A$12,IFERROR(MATCH(B1,A1:A12,0),MATCH(B1,A1:A12,1)+1),1)

[benishiryo]

you're very welcome.

A1:A12>=B1
this is a logical test to find out if A1:A12 is greater or equals to B1. if B1 is 13, it will return:
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE}
the 1st TRUE is the answer we need

MATCH(TRUE,A1:A12>=B1,0)
you can then use a MATCH formula to return the position the 1st TRUE is in. however, this will become an array formula, where you have to press CTRL + SHIFT + ENTER to confirm.

MATCH(TRUE,INDEX((A1:A12>=B1),),0)
by storing it inside the INDEX formula, i can make it not become an array formula. you can do this by putting in the logical test inside the array portion of the INDEX formula, and put in nothing (like what i did) or 0 in the row_num portion of the INDEX formula. it is equivalent to putting
MATCH(TRUE,INDEX((A1:A12>=B1),0),0)

hope that helps

[Teethless mama]

Thnaks benishiryo very much
Would u plz explain how its working ?
specially what is ),),0)) at last

another one for you to know

),),))

[arctushar]

Thanks FDibbins
yours one is more simple
I like it

[FDibbins]

Happy to help. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neet and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)