Match Index Formula?

I have a table which I download from Access on a regular basis in which the columns may change.

I want to assign the "P" values underneath the correct column in a static table in Excel.

Please see attched example
Book1.xls
Microsoft Excel

Last Comment
dlmille

8/22/2022 - Mon
Saqib Husain

You can enter this formula in B10 and copy down and across

=IFERROR(IF(INDEX(\$B\$3:\$F\$5,MATCH(\$A18,\$A\$3:\$A\$5,0),MATCH(B\$17,\$B\$2:\$F\$2,0))="","",INDEX(\$B\$3:\$F\$5,MATCH(\$A18,\$A\$3:\$A\$5,0),MATCH(B\$17,\$B\$2:\$F\$2,0))),"")
dlmille

If your data is numeric, then we can use this:

[B10]=SUMPRODUCT((\$B\$3:\$F\$5)*(\$B\$2:\$F\$2=B\$9)*(\$A\$3:\$A\$5=\$A14))
Copy down and across

However, this will result in Zero at intersections that don't exist.  of course, you could put an IF statement around it to turn it to blank if not found.

=IF(equation=0,"",equation)

See attached.

Dave
Book1.xls
dlmille

If the data is not numeric, then the INDEX/MATCH approach works fine:

[B10]=IF(ISERROR(INDEX(\$B\$3:\$F\$5,MATCH(\$A10,\$A\$3:\$A\$5,0),MATCH(B\$9,\$B\$2:\$F\$2,0))),"",INDEX(\$B\$3:\$F\$5,MATCH(\$A10,\$A\$3:\$A\$5,0),MATCH(B\$9,\$B\$2:\$F\$2,0)))

And if you want blanks to appear as blank on the MATCH condition (e.g., if no P is there, you want blank, not zero), then use this:
[b10]=IF(OR(ISERROR(MATCH(\$A10,\$A\$3:\$A\$5,0)),ISERROR(MATCH(B\$9,\$B\$2:\$F\$2,0))),"",IF(INDEX(\$B\$3:\$F\$5,MATCH(\$A10,\$A\$3:\$A\$5,0),MATCH(B\$9,\$B\$2:\$F\$2,0))=0,"",INDEX(\$B\$3:\$F\$5,MATCH(\$A10,\$A\$3:\$A\$5,0),MATCH(B\$9,\$B\$2:\$F\$2,0))))

Copy down and across.

See attached.

Dave
Book1.xls
fitaliano