Lookup/Index match with multiple criteria

Welcome to the forum.

It will be much easier to get the results if you re-arrange your lower table a bit.
1. Put an extra column 'Task' after the 'Name' and move the tasks to this column;
2. Copy the ID and Name to each row;
3. Optionally, remove the blank row against each name.

The reason for (1) is to be able to clearly separate tasks from names in the formula below. The reason for (2) is to avoid a hugely complicated check of where the ID and Name can be found for each task.

Now you can use an Index-Match formula (yes, you were right) in D14. It uses concatenation (the & symbol) to look up a combination of the ID-Name-Task-Sprint# against the concatenated columns from the upper table, then return the Status.

Formula:

Please Login or Register  to view this content.

Important: this is an array formula so needs to be entered using Ctrl-Shift-Enter not just Enter. You will know the array is active when you see curly brackets { } appear around your formula - don't try to enter them yourself. If you do not Ctrl-Shift-Enter an array formula you will get an error or a clearly incorrect result. If that happens, just click into the formula bar and try again.
Also make sure you have the $ symbols correct ($A13 but D$13, for example) as these specify how the formula works as you drag it across then down (or down then across) to G21.


Lastly, you can optionally add some conditional formatting to 'grey-out' the duplicated IDs and Names in the lower table - just to make it easier to read.


I've attached your file with the above changes and formula working. Hope that does what you want.

I made the assumption that the format of the table could not be changed (why else would anyone put two different types of variables in the same column, right?!)
Anyway, here's what I came up with (again, it is an array formula so use CNTRL SHFT ENTER)
In B14 copied across and down

Formula:

Please Login or Register  to view this content.

And here's the spreadsheet to prove it.

You're welcome, glad I could help and thanks for marking the thread as Solved.

@ChemistB: very nice, I went for the lazy option first!

Nothing wrong with the lazy option.

Laziness is the mother of invention.
or, Work smarter, not harder.