Match? Maybe?

mhenk

Well-known Member
Joined
Jun 13, 2005
Messages
591
I'm dealing with an irritating spreadsheet that my boss threw together and now I'm trying to summarize.

Is there anyway to return the value immediately under the occurance of a certain number in a column? (col. E, to be exact)

Example:

1
24507
34523
2343

2
345345
5645

3
124234


I would like to be able to create a summary table off to the right of all this data, and return something like this
1 | 24507
2 | 245245
3 | 123234

Any assistance would be grand!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,016
Office Version
  1. 365
=INDEX(E:E,MATCH(1,E:E,0)+1) will return the number below the first 1
=INDEX(E:E,MATCH(2,E:E,0)+1) will return the number below the first 2, etc.

Of course, if your data is
1
2
something else

2
foo
bar

The first "2" will throw off this calculation. Hopefully, from your sample, this isn't an issue.
 

mhenk

Well-known Member
Joined
Jun 13, 2005
Messages
591
Right, it's not an issue,thanks. Works perfectly, btw!
 

Forum statistics

Threads
1,141,681
Messages
5,707,795
Members
421,528
Latest member
datdude151

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top