Finding the next matching value

[ellie2014]

Hello Everyone,

I am having trouble finding a formula to get the next matching number. Please see example uploaded. Expected output is on Sheet2. Is there a formula that can make this happen?Example.xlsx

[TMS]

You can do it using a Helper Row on Sheet1

B4:

Please Login or Register  to view this content.

and copy across.

Then, on Sheet2, use: Row 4:

Please Login or Register  to view this content.

committed with Ctrl-Shift-Enter and copied down.

See the updated example.


Regards, TMS

[tigertiger]

Hello Everyone,

I am having trouble finding a formula to get the next matching number. Please see example uploaded. Expected output is on Sheet2. Is there a formula that can make this happen?Attachment 335961

Try the formula for cell C4 at Sheet2, likes this:

Please Login or Register  to view this content.

[ellie2014]

Thanks to both of you. They both worked!

[martindwilson]

well if you are using a helper then
in sheet1!b1
=RANK(B3,$B$3:$Q$3)+COUNTIF($B$3:B3,B3)-1 filled acros
then
sheet2!c4 is
=INDEX(Sheet1!$B$2:$Q$2,,MATCH(ROWS($1:1),Sheet1!$B$1:$Q$1,0)) filled down

[TMS]

You're welcome. Thanks for the rep.


I like Martin's solution because, although it uses a Helper row, it doesn't need an Array Formula. And I like Tiger's solution too as it's self contained ... though I would really struggle to work out a formula like that from scratch.

But hey, three different and working solutions in less than 40 minutes ... can't be bad