I'm working with a data in which there are multiple sets of information in the same column. This is making it difficult to pick out the data I need as it always returns the first result. I am trying to find a way to ensure that the column result returned in a search is based on a different column's value. For example:
| Name/Date | 01/01/2022 | 02/01/2022 | 03/01/2022 | 04/01/2022 | 05/01/2022 |
|---|---|---|---|---|---|
| Bob | 1 | 7 | 2 | 6 | 1 |
| Jane | 1 | 7 | 9 | 3 | 1 |
| Jimmy | 8 | 7 | 5 | 4 | 2 |
| Robin | 1 | 2 | 9 | 6 | 2 |
| Batman | 4 | 7 | 6 | 6 | 8 |
| 06/01/2022 | 07/01/2022 | 08/01/2022 | 09/01/2022 | 10/01/2022 | |
| Bob | 4 | 1 | 4 | 2 | 12 |
| Jane | 6 | 21 | 9 | 3 | 1 |
| Jimmy | 8 | 2 | 5 | 4 | 2 |
| Robin | 8 | 5 | 0 | 6 | 2 |
| Batman | 5 | 5 | 6 | 6 | 8 |
If I wanted to yield the number for Jane on 07/01/2022 (which is 21), is there any way of returning this? I've been able to use =MATCH to pull the correct column based on the date search criteria, but I cannot see a viable way of pulling for a particular person when their name appears multiple times in the same column. Attempting a lookup will return the first result for the name hit (so in the case of my Jane example, it would return row 3 instead of 9). I'm looking to be able to enter a name and a date, and it returns the result from that part of the array.
Is such a thing possible, please? If not, is there any workaround that may help to provide me the same result?
Thanks in advance for your assistance!
- This is being attempting in Office 365.
CodePudding user response：
Assuming:
- ms365 (specifically access to the BETA-channel);
- Equal intervals with the same names at the same postions;
Formula in I4:
=LET(X,WRAPCOLS(TOCOL(B1:F12,,1),6),SUM((A1:A6=I3)*FILTER(X,TAKE(X,1)=I2)))
CodePudding user response：
Assuming your data starts in row 1 A1:F12 - data range B17 - Jane B18 - 07/01/2022
=LET(data,A1:F12,names,A1:A12,lookupname,B17,lookupdate,B18,INDEX(data,MIN(LET(rowlist,IF(names=lookupname,ROW(names),0),IF(rowlist>MAX(IF(data=lookupdate,ROW(data),0)),rowlist,""))),MAX(IF(data=lookupdate,COLUMN(data),0))))