Forum Discussion
Find/return the next value in the column list
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")
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")
SergeiBaklan What if you don't know the next value in the column? I have a column of random dates and next to it is a column of random values that don't populate every cell. I want to create a table that picks up just the date where there is a value, and then the corresponding value. Thanks!
7/28/2017 6,900,439.07 8/1/2017 9/1/2017 9/21/2017 52,618.28 10/1/2017 10/20/2017 302,730.65 11/1/2017 11/22/2017 293,637.39 12/1/2017 As SergeiBaklan says, it depends on the version of Excel that you are using.
= XLOOKUP( lookupDate, IF(value>0, date), value,, 1)I think XLOOKUP is Excel 2019 and on. Th final '1' selects the next higher value rather than the next lower that would be returned by the old LOOKUP function.
- Many thank.
wazza2040 , you are welcome
