Lookup in array based on weekday

My excel abilities are moderate, and I haven't used it for about a year, so apologies if this is a daft question.

I have a calendar spreadsheet that needs to lookup some information from an array. Both the calendar date and the array data are populated by a user from a userform.

I need to lookup information from the array, based on what day of the week is in a particular cell (but the day can be any weekday depending on what starting date the user entered). I started writing an 'IF' formula - 'if cell is a monday, take the info from this cell', but it occurred to me that even if that worked with 5 different ifs in every cell, there must be a more efficient way of doing this, perhaps with H or Vlookup.

Attached is a dummy of my problem, with the desired result on the second sheet in italics. Please could someone help me out? Thank you very much.

Try this in cell C4 on the Desired Result Tab. Put Monday in Cell C2

Please Login or Register  to view this content.

Try placing the following formula in cell C4 of the Desired Result sheet and drag-copying it down as needed:

=IF(LEN(VLOOKUP(B4,db!$A$3:$F$12,MATCH(TEXT(B$2,"dddd"),db!$A$2:$F$2,0),0)),VLOOKUP(B4,db!$A$3:$F$12,MATCH(TEXT(B$2,"dddd"),db!$A$2:$F$2,0),0),"")

Try
=VLOOKUP(B4,db!$A$3:$F$12,WEEKDAY($C$2),FALSE)

In C4 then copy to other required range.

Please Login or Register  to view this content.

Originally Posted by Root_

Try placing the following formula in cell C4 of the Desired Result sheet and drag-copying it down as needed:

=IF(LEN(VLOOKUP(B4,db!$A$3:$F$12,MATCH(TEXT(B$2,"dddd"),db!$A$2:$F$2,0),0)),VLOOKUP(B4,db!$A$3:$F$12,MATCH(TEXT(B$2,"dddd"),db!$A$2:$F$2,0),0),"")

This worked , thank you

You are welcome.