Forum Discussion
Solved
Find/return the next value in the column list
Hi, I have a list of numbers in a column. A have a number in another cell that has been selected from that list. Is it possible to find this number in the list (a la vlookup) and then acc...
You may find with MATCH() position of the number in the list, and with INDEX() return value from the cell in next position. If, for exmple, your list is in column A and number to search is in cell B1, it could be
=IFERROR(INDEX(A:A,MATCH(B1,A:A,0)+1),"no such number")
On which Excel version you are?
Try to open attached file if it works in your environment. It replicates your sample
Thanks SergeiBaklan.
even your excel shows the formula as
Mine is 2016. maybe the code is not supported.
Okay, for 2016 it could be
In E1 =IFERROR( INDEX( A:A, AGGREGATE(15,6, 1/(B:B<>"")*(ROW(A:A)-ROW($A$1)+1), ROW()-ROW($E$1)+1 ) ), "" ) In F1 =IFERROR( INDEX( B:B, AGGREGATE(15,6, 1/(B:B<>"")*(ROW(A:A)-ROW($A$1)+1), ROW()-ROW($E$1)+1 ) ), "" )and drag them down till empty cells appear. If you ranges start in another cells above shall be adjusted accordingly.
- Yes this works! thank you.
RADS_JAS1360 , you are welcome