We help IT Professionals succeed at work.

# Match Index Formula?

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.

Book1.xls
Comment
Watch Question

## View Solution Only

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

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