Formula help

[Weefergie56]

Hello, I am trying to create an excel timesheet and overcome some bits myself but stumped on this formula.



I have a row with the days of the week and hours per day in the row directly underneath . What I am trying to do is have a cell in the spreadsheet check if the day in a cell matches a day in the index row to insert the hours per day in the cell underneath the index row into that cell. These are not the exact cell and rows I have but I am trying to make it easier to read.



A1:G1 (Mon - Sun)

A2:G2 (Hours per day)

A4 (contains the day of the week - ie Wed)

A5 (I want the formula to check what day A4 matches in A1:G1 and insert the hours of work from the cell below that day in row A2 into cell A5)

	A	B	C	D	E	F	G
A1	Mon	Tue	Wed	Thur	Fri	Sat	Sun
A2	8​	8​	12​	8​	8​	0​	0​
A3
A4	Wed	Thur	Fri	Sat	Sun	Mon	Tue
A5	12​	​	​	​	​	​	​

I have tried these formulas below in A5:



I have tried using an If function but it is pulling info from cells that it shouldn't in row A3:

=IF(A4="true",A1:G1,(MATCH(A5,A2:G2)))



I have also tried using an index function and played about with the colum and row value but getting a are reference error:

=INDEX(A1:G7,MATCH(A4,A1:G7,0),2)



I think my problem is that I found it from an example matching column info rather than rows.



Any advice appreciated. Thank you!

 

[etaf]

=INDEX(A1:G7,MATCH(A4,A1:G7,0),2)
try
=INDEX($A$2:$G$2,MATCH(A4,$A$1:$G$1,0))
copy across

Book12
	A	B	C	D	E	F	G
1	Mon	Tue	Wed	Thur	Fri	Sat	Sun
2	8	8	12	8	8	0	0
3
4	Wed	Thur	Fri	Sat	Sun	Mon	Tue
5	12	8	8	0	0	8	8
Sheet1

Cell Formulas
Range	Formula
A5:G5	A5	=INDEX($A$2:$G$2,MATCH(A4,$A$1:$G$1,0))

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Mon	Tue	Wed	Thur	Fri	Sat	Sun
2	8	8	12	8	8	0	0
3
4	Wed	Thur	Fri	Sat	Sun	Mon	Tue
5	12	8	8	0	0	8	8
Data

Cell Formulas
Range	Formula
A5:G5	A5	=XLOOKUP(A4,$A$1:$G$1,$A$2:$G$2,"")

 

[Weefergie56]

Both solutions work perfectly! thank you so much, I owe you both a coffee

I appreciate your speedy response too, I've been headscratching this for a while this morning.

 

[etaf]

you are welcome

 

[Fluff]

Glad we could help & thanks for the feedback.