Finding the row-level

[baidya91]

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:

Level	1	2	3	4	5	6	7
1	17000	17600				22700	24700
2	17500	18100				23400	25400
3	18000	18600				24100	26200
4	18500	19200				24800	27000
5	19100	19800				25500	27800
6	19700	20400				26300	28600
7	20300	21000				27100	29500

 

[Fluff]

My formula needs to be confirmed with Ctrl Shift Enter, rather than just enter.

 

[baidya91]

o

My formula needs to be confirmed with Ctrl Shift Enter, rather than just enter.

OK, Sir

 

[Snakehips]

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

 

[baidya91]

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?

 

[Fluff]

What if there isn't a higher number in that column?

 

[baidya91]

What if there isn't a higher number in that column?

Figure will be either equal or higher, Sir.......

 

[Fluff]

How about
=AGGREGATE(15,6,(ROW(B2:B8)-ROW(B2)+1)/(J2<=INDEX(B2:H8,0,MATCH(J1,B1:H1,0))),1)

 

[baidya91]

How about
=AGGREGATE(15,6,(ROW(B2:B8)-ROW(B2)+1)/(J2<=INDEX(B2:H8,0,MATCH(J1,B1:H1,0))),1)

Thanks

 

[Fluff]

You're welcome