Finding the row-level

baidya91

Board Regular
Joined
Jun 1, 2016
Messages
147
In an excel table there are two levels of figures (column-level & row-level which are numbered). Now if the number of column is fixed and we find a figure from the table equal to or nearest to a certain figure, what will be the number of row-level? In the following the number of column-level is 6 and we want to find a figure equal or nearest to 24500, the row level number will be 4 and the figure will be 24800. How shall we find the row-level number 4 by using an excel formula. Please help. Thanks in advance:

Level1234567
117000176002270024700
217500181002340025400
318000186002410026200
418500192002480027000
519100198002550027800
619700204002630028600
720300210002710029500
 
My formula needs to be confirmed with Ctrl Shift Enter, rather than just enter.
 
Upvote 0

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sorry. I must have been half asleep and posted with the direct reference to table, column 6.
Otherwise it would have been virtually identical to Fluff's
=MATCH(MIN(ABS(INDEX(D5:J11,0,E2)-D2)),ABS(INDEX(D5:J11,0,E2)-D2),0)
Ctrl + Shift + Enter
 
Upvote 0
My formula needs to be confirmed with Ctrl Shift Enter, rather than just enter.
Sir, What will be the revised formula if the figure searched from the table is equal to another figure or the next higher figure? For example, suppose, there is no figure equal to 36597 in the table but the next higher figure is 37600 but the previous figure is 36500. Now I want to select the next higher figure, i.e, 37600. What will be the revised formula in this case?
 
Upvote 0
What if there isn't a higher number in that column?
 
Upvote 0
How about
=AGGREGATE(15,6,(ROW(B2:B8)-ROW(B2)+1)/(J2<=INDEX(B2:H8,0,MATCH(J1,B1:H1,0))),1)
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

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
Back
Top