I'm trying to solve this problem since some days now but it seems I have reached a dead end. Maybe someone would be able to help me.

I have two sheets. The first one contains the list of my clients and their delivery number depending of the weekday.

In my second sheet I would like to get the delivery number of the client (red cells) depending of the weekday I select (yellow cells).

I tried VLOOKUP formula, INDEX/MATCH, QUERY but I wasn't able to find a way to get the delivery number depending of the client's name and the weekday. I think the main issue is that in the first sheet the weekday is a column title.

Maybe the solution is simply to build my tables differently...

Thank you for your help

CodePudding user response：

You can try something like this, assuming A2 and B2 the cells of first name and first day to look:

=INDEX(Sheet1!$1:$1000,MATCH(A2,Sheet1!$A:$A,0),MATCH(B2,Sheet1!$1:$1,0))

Or, if you want this same formula for the full column:

=byrow(A2:A,lambda(each,if(each="","",INDEX(Sheet1!$1:$1000,MATCH(each,Sheet1!$A:$A,0),MATCH(offset(each,0,1),Sheet1!$1:$1,0)))))

CodePudding user response：

Also doable (are perhaps more simply) using a MAP/FILTER; with your 'Caption 1' table in Sheet1!A1:D4 and your 'Caption 2' table at the top-left of Sheet2, the following in Sheet2!C2 gives you the delivery number for a many names/days as you enter in the columns alongside:

=map(A2:A,B2:B,lambda(name,day,ifna(filter(filter(Sheet1!B2:D4,Sheet1!A2:A4=name),Sheet1!B1:D1=day))))

N.B. The IFNA blanks out errors for those rows where a Name/Day pair hasn't been entered yet. Extend the ranges in the filter to suit your real data.

CodePudding user response：

all you need is simple vlookup:

=INDEX(IFNA(VLOOKUP(A9:A11&B9:B11, 
 SPLIT(FLATTEN(A2:A4&B1:D1&"​​"&B2:D4), "​​"), 2, )))