Return value based on column header

[The Power Loon]

I have a list of values in Table column B with each value corresponding to one of the headers in the table (I attached a sample of the file below). What I'm looking for is a formula or macro that will take the value in column B, determine the corresponding value in the column with the header of the same name, and return it in Column C.

In the sample table below, C2 should be -1.26%. This is because value in B2 is "1005", and the value in row 2 of column 1005 is "-1.26%".

Product	Time	Value	950	955	1000	1005	1010	1015	1020
AAA	1005		-11.41%	-2.45%	0.00%	-1.26%	-2.55%	1.31%	1.94%
BBB	955		11.28%	3.48%	2.34%	-0.29%	1.86%	-5.34%	-5.20%
CCC	1020		-3.09%	-3.72%	-1.10%	-4.47%	-1.75%	0.00%	-1.19%
DDD	1010		-6.11%	11.24%	-1.60%	-1.08%	6.56%	-2.05%	2.09%

I would do a nested IF, but the full sheet has well over 100 columns, making that somewhat unfeasible. Anyone know a method by which to do this?

 

[FormR]

Hi, something like this maybe..

Book2
	A	B	C	D	E	F	G	H	I	J
1	Product	Time	Value	950	955	1000	1005	1010	1015	1020
2	AAA	1005	-1.26%	-11.41%	-2.45%	0.00%	-1.26%	-2.55%	1.31%	1.94%
3	BBB	955	3.48%	11.28%	3.48%	2.34%	-0.29%	1.86%	-5.34%	-5.20%
4	CCC	1020	-1.19%	-3.09%	-3.72%	-1.10%	-4.47%	-1.75%	0.00%	-1.19%
5	DDD	1010	6.56%	-6.11%	11.24%	-1.60%	-1.08%	6.56%	-2.05%	2.09%
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=XLOOKUP(B2,$D$1:$J$1,D2:J2,"NOT FOUND!")

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Product	Time	Value	950	955	1000	1005	1010	1015	1020
2	AAA	1005	-1.26%	-11.41%	-2.45%	0.00%	-1.26%	-2.55%	1.31%	1.94%
3	BBB	955	3.48%	11.28%	3.48%	2.34%	-0.29%	1.86%	-5.34%	-5.20%
4	CCC	1020	-1.19%	-3.09%	-3.72%	-1.10%	-4.47%	-1.75%	0.00%	-1.19%
5	DDD	1010	6.56%	-6.11%	11.24%	-1.60%	-1.08%	6.56%	-2.05%	2.09%
Data

Cell Formulas
Range	Formula
C2:C5	C2	=SUMIFS(D2:J2,$D$1:$J$1,B2)

 

[The Power Loon]

Hi, something like this maybe..

Book2
	A	B	C	D	E	F	G	H	I	J
1	Product	Time	Value	950	955	1000	1005	1010	1015	1020
2	AAA	1005	-1.26%	-11.41%	-2.45%	0.00%	-1.26%	-2.55%	1.31%	1.94%
3	BBB	955	3.48%	11.28%	3.48%	2.34%	-0.29%	1.86%	-5.34%	-5.20%
4	CCC	1020	-1.19%	-3.09%	-3.72%	-1.10%	-4.47%	-1.75%	0.00%	-1.19%
5	DDD	1010	6.56%	-6.11%	11.24%	-1.60%	-1.08%	6.56%	-2.05%	2.09%
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=XLOOKUP(B2,$D$1:$J$1,D2:J2,"NOT FOUND!")

For whatever reason, it only returns "Not Found" when I tried it, even on the sample. However, no worries. The formula that Fluff sent worked out for me.
Thank you for your time and consideration of this issue.

 

[The Power Loon]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Product	Time	Value	950	955	1000	1005	1010	1015	1020
2	AAA	1005	-1.26%	-11.41%	-2.45%	0.00%	-1.26%	-2.55%	1.31%	1.94%
3	BBB	955	3.48%	11.28%	3.48%	2.34%	-0.29%	1.86%	-5.34%	-5.20%
4	CCC	1020	-1.19%	-3.09%	-3.72%	-1.10%	-4.47%	-1.75%	0.00%	-1.19%
5	DDD	1010	6.56%	-6.11%	11.24%	-1.60%	-1.08%	6.56%	-2.05%	2.09%
Data

Cell Formulas
Range	Formula
C2:C5	C2	=SUMIFS(D2:J2,$D$1:$J$1,B2)

This worked perfectly, thank you for your response to this question.

 

[FormR]

For whatever reason, it only returns "Not Found" when I tried it, even on the sample

It's possible that the headers are text and the column B values are numbers (SUMIFS() is less sensitive to that) - you could try one of these alternatives.

=XLOOKUP(""&B2,$D$1:$J$1,D2:J2,"NOT FOUND!")
or
=XLOOKUP(0+B2,$D$1:$J$1,D2:J2,"NOT FOUND!")

I'd suspect that the XLOOKUP() option would give better performance, especially as..

the full sheet has well over 100 columns

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[The Power Loon]

It's possible that the headers are text and the column B values are numbers (SUMIFS() is less sensitive to that) - you could try one of these alternatives.

=XLOOKUP(""&B2,$D$1:$J$1,D2:J2,"NOT FOUND!")
or
=XLOOKUP(0+B2,$D$1:$J$1,D2:J2,"NOT FOUND!")

I'd suspect that the XLOOKUP() option would give better performance, especially as..

=XLOOKUP(""&B2,$D$1:$J$1,D2:J2,"NOT FOUND!")

This one worked perfectly as well. Thank you for pursuing this.