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

 

[sandy666]

is it mandatory?

or nearest to

 

[baidya91]

sandy666 Sir, It will help me to work out a problem......

 

[sandy666]

I am not using formula(s) but Power Query so I can't help with or nearest to
maybe someone else

 

[baidya91]

I am not using formula(s) but Power Query so I can't help with or nearest to
maybe someone else

Ok, if someone helps me.....

 

[baidya91]

Ok, if someone helps me.....

Please help ......

 

[Snakehips]

Maybe like.....

Book1
	C	D	E	F	G	H	I	J
1		Find	Column	=	Row	Value
2		24500	6	=	4	24800
3
4	Level	1	2	3	4	5	6	7
5	1	17000	17600				22700	24700
6	2	17500	18100				23400	25400
7	3	18000	18600				24100	26200
8	4	18500	19200				24800	27000
9	5	19100	19800				25500	27800
10	6	19700	20400				26300	28600
11	7	20300	21000				27100	29500
Sheet12

Cell Formulas
Range	Formula
G2	G2	{=MATCH(MIN(ABS(D2-I5:I11)),ABS(D2-I5:I11),0)}
H2	H2	=INDEX(D5:J11,G2,E2)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Hope that helps.

 

[Fluff]

How about

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Level	1	2	3	4	5	6	7		6
2	1	17000	17600				22700	24700		24500	4
3	2	17500	18100				23400	25400
4	3	18000	18600				24100	26200
5	4	18500	19200				24800	27000
6	5	19100	19800				25500	27800
7	6	19700	20400				26300	28600
8	7	20300	21000				27100	29500
Macro

Cell Formulas
Range	Formula
K2	K2	=MATCH(MIN(ABS(INDEX(B2:H8,0,J1)-J2)),ABS(INDEX(B2:H8,0,J1)-J2),0)

 

[baidya91]

How about

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Level	1	2	3	4	5	6	7		6
2	1	17000	17600				22700	24700		24500	4
3	2	17500	18100				23400	25400
4	3	18000	18600				24100	26200
5	4	18500	19200				24800	27000
6	5	19100	19800				25500	27800
7	6	19700	20400				26300	28600
8	7	20300	21000				27100	29500
Macro

Cell Formulas
Range	Formula
K2	K2	=MATCH(MIN(ABS(INDEX(B2:H8,0,J1)-J2)),ABS(INDEX(B2:H8,0,J1)-J2),0)

#N/A message is coming in result field (K2 Cell)

 

[baidya91]

Maybe like.....

Book1
	C	D	E	F	G	H	I	J
1		Find	Column	=	Row	Value
2		24500	6	=	4	24800
3
4	Level	1	2	3	4	5	6	7
5	1	17000	17600				22700	24700
6	2	17500	18100				23400	25400
7	3	18000	18600				24100	26200
8	4	18500	19200				24800	27000
9	5	19100	19800				25500	27800
10	6	19700	20400				26300	28600
11	7	20300	21000				27100	29500
Sheet12

Cell Formulas
Range	Formula
G2	G2	{=MATCH(MIN(ABS(D2-I5:I11)),ABS(D2-I5:I11),0)}
H2	H2	=INDEX(D5:J11,G2,E2)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Hope that helps.

Works fine but column level No 6 is not included in the first formula. Hence that does not serve my purpose.