Multi Dimension Index-Match

0
votes

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.

2
google-sheetsmatchgoogle-sheets-formulaarray-formulasgs-vlookup

2 Answers

0
votes

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))

enter image description here

0
votes

delete everything in range C4:Z

paste this in C4 cell and drag to the right:

=ARRAYFORMULA(IFNA(VLOOKUP($A4:$A, {'Mon-Sun'!$A3:$A, INDIRECT("Mon-Sun!"&ADDRESS(3,                  
 MATCH(IF(C2="", B2, C2), 'Mon-Sun'!1:1, 0)+IF(C2="", 1, 0), 4)&":"&ADDRESS(ROWS('Mon-Sun'!A:A),
 MATCH(IF(C2="", B2, C2), 'Mon-Sun'!1:1, 0)+IF(C2="", 1, 0), 4))}, 2, 0)))

enter image description here