match column data to an horizontal array then return value

[Jescanilla]

Good afternoon.

as the title says im trying to match a number in a column to an horizontal array then return the value at the side indicated by the column.
I have try to use index and match for this porpouse without good result.

it must look horizontally for the value given in the column vertically and the copy the value at the left of the column with the data.

Preparacion cambio componente anual detalle semanal.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2			12950	13101,2	13252,4	13403,6	13554,8	13706	13857,2	14008,4	14159,6	14310,8	14462	14613,2	14764,4
3	HH	DATA
4	52	14460											52
5	34	13700						34
Sheet2 (2)

hope you can help me.

 

[kweaver]

I'm unclear on how you determine those 2 values? Are you looking for (for example) when 13700 is closest to D2:P2?
What other options are possible for DATA in the C column? Fractional?

 

[Anonymous1378]

Not too sure about your description, but try this?

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2			12950	13101.2	13252.4	13403.6	13554.8	13706	13857.2	14008.4	14159.6	14310.8	14462	14613.2	14764.4
3	HH	DATA
4	52	14460											52
5	34	13700						34
Sheet2

Cell Formulas
Range	Formula
D4:P5	D4	=IF(D$2:P$2=INDEX(D$2:P$2,XMATCH(C4:C5,D$2:P$2,1)),B4:B5,"")
Dynamic array formulas.

 

[Jescanilla]

I'm unclear on how you determine those 2 values? Are you looking for (for example) when 13700 is closest to D2:P2?
What other options are possible for DATA in the C column? Fractional?

Yes. i need to position to the nearest value of D2:P2.
And fractional are not neccesary.

Cheers

 

[Jescanilla]

Not too sure about your description, but try this?

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2			12950	13101.2	13252.4	13403.6	13554.8	13706	13857.2	14008.4	14159.6	14310.8	14462	14613.2	14764.4
3	HH	DATA
4	52	14460											52
5	34	13700						34
Sheet2

Cell Formulas
Range	Formula
D4:P5	D4	=IF(D$2:P$2=INDEX(D$2:P$2,XMATCH(C4:C5,D$2:P$2,1)),B4:B5,"")
Dynamic array formulas.

i use the exact formula and i got #SPILL! error

 

[Anonymous1378]

i use the exact formula and i got #SPILL! error

Remove the filled cells in the vicinity?

 

[Jescanilla]

Remove the filled cells in the vicinity?

My bad the formula work perfect.
thank you.