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.
