SOLVED
Home

Find/return the next value in the column list

%3CLINGO-SUB%20id%3D%22lingo-sub-1013402%22%20slang%3D%22en-US%22%3EFind%2Freturn%20the%20next%20value%20in%20the%20column%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1013402%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20list%20of%20numbers%20in%20a%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20have%20a%20number%20in%20another%20cell%20that%20has%20been%20selected%20from%20that%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20possible%20to%20find%20this%20number%20in%20the%20list%20(a%20la%20vlookup)%20and%20then%20access%2Freturn%20the%20value%20that%20is%20in%20the%20next%20cell%20in%20the%20list%2C%20the%20value%20in%20the%20cell%20below%20the%20current%20one%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1013402%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1013450%22%20slang%3D%22en-US%22%3ERe%3A%20Find%2Freturn%20the%20next%20value%20in%20the%20column%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1013450%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F459830%22%20target%3D%22_blank%22%3E%40wazza2040%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20find%20with%20MATCH()%20position%20of%20the%20number%20in%20the%20list%2C%20and%20with%20INDEX()%20return%20value%20from%20the%20cell%20in%20next%20position.%20If%2C%20for%20exmple%2C%20your%20list%20is%20in%20column%20A%20and%20number%20to%20search%20is%20in%20cell%20B1%2C%20it%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(INDEX(A%3AA%2CMATCH(B1%2CA%3AA%2C0)%2B1)%2C%22no%20such%20number%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1013454%22%20slang%3D%22en-US%22%3ERe%3A%20Find%2Freturn%20the%20next%20value%20in%20the%20column%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1013454%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F459830%22%20target%3D%22_blank%22%3E%40wazza2040%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EVLOOKUP%3C%2FSTRONG%3E%20always%20has%20been%20a%20flawed%20function%20for%20a%20number%20of%20reasons%2C%26nbsp%3B%20%3CSTRONG%3EINDEX%2FMATCH%3C%2FSTRONG%3E%20would%20be%20better%20here.%26nbsp%3B%20Calling%20you%20list%20and%20number%20by%20those%20names%2C%20the%20simplest%20formula%20is%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20INDEX(%20list%2C%201%20%2B%20MATCH(%20number%2C%20list%2C%200%20)%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3EInstead%20of%20adding%201%20to%20the%20index%20it%20is%20also%20possible%20to%20define%20'offsetList'%20to%20be%20a%20range%20one%20cell%20down%20from%20the%20initial%20list%3A%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20INDEX(%20offsetList%2C%20MATCH(%20number%2C%20list%2C%200%20)%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3ESwitching%20to%20the%20latest%20versions%20of%20Office%20365%2C%20one%20has%20the%20new%20XLOOKUP%20function%20which%20replaces%20other%20lookup%20function%20in%20almost%20all%20circumstances.%26nbsp%3B%20Using%20the%20offset%20list%20once%20more%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20XLOOKUP(%20number%2C%20list%2C%20offsetList%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3Eor%20if%20volatile%20functions%20are%20not%20a%20problem%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20OFFSET(%20XLOOKUP(%20number%2C%20list%2C%20list%20)%2C%201%2C%200%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1013488%22%20slang%3D%22en-US%22%3ERe%3A%20Find%2Freturn%20the%20next%20value%20in%20the%20column%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1013488%22%20slang%3D%22en-US%22%3EMany%20thank.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1013895%22%20slang%3D%22en-US%22%3ERe%3A%20Find%2Freturn%20the%20next%20value%20in%20the%20column%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1013895%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F459830%22%20target%3D%22_blank%22%3E%40wazza2040%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
wazza2040
New Contributor

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.

4 Replies
Solution

@wazza2040 

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")

 

@wazza2040 

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.

@wazza2040 , you are welcome

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies