SOLVED

# Find/return the next value in the column list

Copper Contributor

# 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 access/return the value that is in the next cell in the list, the value in the cell below the current one?

Thanks.

13 Replies
best response confirmed by wazza2040 (Copper Contributor)
Solution

# Re: 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")``

# Re: Find/return the next value in the column list

VLOOKUP always has been a flawed function for a number of reasons,  INDEX/MATCH would be better here.  Calling you list and number by those names, the simplest formula is

= INDEX( list, 1 + MATCH( number, list, 0 ) )

Instead of adding 1 to the index it is also possible to define 'offsetList' to be a range one cell down from the initial list:

= INDEX( offsetList, MATCH( number, list, 0 ) )

Switching to the latest versions of Office 365, one has the new XLOOKUP function which replaces other lookup function in almost all circumstances.  Using the offset list once more

= XLOOKUP( number, list, offsetList )

or if volatile functions are not a problem

= OFFSET( XLOOKUP( number, list, list ), 1, 0 )

Many thank.

# Re: Find/return the next value in the column list

@wazza2040 , you are welcome

# Re: Find/return the next value in the column list

@Sergei Baklan   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

# Re: Find/return the next value in the column list

Depends on your Excel version, for example

# Re: Find/return the next value in the column list

As @Sergei Baklan 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.

# Re: Find/return the next value in the column list

@Sergei Baklan it is showing the function as invalid.

# Re: Find/return the next value in the column list

On which Excel version you are?

Try to open attached file if it works in your environment. It replicates your sample

# Re: Find/return the next value in the column list

Thanks @Sergei Baklan.

even your excel shows the formula as

Mine is 2016. maybe the code is not supported.

# Re: Find/return the next value in the column list

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.

# Re: Find/return the next value in the column list

Yes this works! thank you.

# Re: Find/return the next value in the column list

1 best response

Accepted Solutions
best response confirmed by wazza2040 (Copper Contributor)
Solution

# Re: 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")``