We help IT Professionals succeed at work.

Match Index Formula?

fitaliano
fitaliano asked
on
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
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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))),"")
Most Valuable Expert 2012
Top Expert 2012

Commented:
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
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

Commented:
It is not working for me, can you put it in the Excel file?
Most Valuable Expert 2012
Top Expert 2012
Commented:
@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

Explore More ContentExplore courses, solutions, and other research materials related to this topic.