I'm stuck with this assignment where I need to use index-match formula to input values from another sheet.
Google Sheet: https://docs.google.com/spreadsheets/d/182b4foaNURrAysA9pQ2rtFinU0U1TnwBDNHEkInbjiQ/edit?usp=sharing
I need to get value from Sheet "Mon-Sun" to show in the sheet "Work hour" using formula. My friend said I should use index-match, but I cannot seem to be able to do it (Only know how to do basic index-match). Been thinking for hours now but cannot find the solution. Please help.
Perhaps the following, which is just an INDEX
with two MATCH
's (ID and day of the week), and a bit of logic to handle your setup with merged cells, as well as whether it's Day
or Night
.
=index('Mon-Sun'!$B$3:$O$6,match($A4,'Mon-Sun'!$A$3:$A$6,0),match(if(isblank(C$2),B$2,C$2),'Mon-Sun'!$B$1:$O$1,0)+if(C$3="Day",0,1))