Excel Formula/function help; 2 step confirmation for a 3rd Value

[HGravelle]

Hello, I have multiple sports team members taking a self assessed skills survey. They will take this survey multiple times. To graph the results I am trying to find the formula/function that will allow me to confirm 2 values, to get the 3rd value to build a graph. ID Number>Survey number>TheirScore.

So for instance:

I need to set up a formula/function that will look for Player #3's answer on Survey #2 to see what they rated their Serving Ability.

"IFS" doesn't seem to work as I need to confirm both the first two to get the third.

HELP PLEASE!

So Looking at Screenshot #1, I want to Confirm first that it is looking at ID# ! in this case (Red) then that it is their response to survey 1 (Blue) if it is True for both in this case I want to get the Attitude value (Green) to populate in the proper place to build my graph (Purple) in Screenshot #2.

Screenshot #1

Screenshot #2

[#2]

please attach a sample (no private/personal info) or provide a link to a shared version in Sharepoint, Onedrive or similar or if those don't work send it via PM. It sounds like a simple LOOKUP or INDEX(MATCH()). I would probably use XLOOKUP. If you read up on those options you might even figure it out yourself and learn even more 🙂

[#3]

Thanks, I added in a sample.

[#4]

so in screen #2 under the S you could use something like:
=FILTER(DataTable[[Speed]:[Knowledge]],(DataTable[ID #]=1)*(DataTable[Self Survey Number?]=1),0)
if there is a chance of multiple rows matching then maybe add an @ in front or INDEX(... ,1,) or get fancy and put it all in a lambda and do a ByCol and Average each set.
BTW the above is using structured reference (or table references) and would work if you formatted the original data table "Format as a Table" from the HOME tab and then named that table (Table tab) "DataTable"
That said, I also don't know where the numbers 1 and 1 come from for that line. If you had the expected #s somehow linked to that second table then you could make it even more dynamic and not have to customize every row/section