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
fitalianoAsked:
Who is Participating?
 
dlmilleCommented:
@fitaliano - I posted my examples in attached workbooks.  Start with my last post, which works with non-numeric and blank if blank is found.

Dave
0
 
Saqib Husain, SyedEngineerCommented:
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))),"")
0
 
dlmilleCommented:
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
0
 
dlmilleCommented:
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
0
 
fitalianoAuthor Commented:
It is not working for me, can you put it in the Excel file?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.