3 criteria for horizontal and vertical matching

**mator** · 12-24-2014, 05:33 AM

I need a formula to find a value from the table where 3 criteria's (that can be changed) are to be used to search that value.
Book2.xlsx

**Glenn Kennedy** · 12-24-2014, 05:44 AM

In the duplicate post, you were asked to post a spreadsheet. A PNG is STILL not a spreadsheet.

**mator** · 12-24-2014, 05:50 AM

Sorry for attaching PNG instead of a spreadsheet. The post has been edited to attach the spreadsheet.

Thanks

**Aladin Akyurek** · 12-24-2014, 06:09 AM

Unmerge all of the merged cells; repeat A and B for every record; and repeat also the cost values for the appropriate records.

When done...

J8, control+shift+enter, not just enter, and copy across:

=INDEX($D$3:$G$18,MATCH(1,IF($B$3:$B$18=J$5,IF($C$3:$C$18=J$6,1)),0),MATCH(J$7,$D$2:$G$2,1))

**samba_ravi** · 12-24-2014, 06:30 AM

J8=SUMPRODUCT((LOOKUP(ROW($A$3:$A$18),ROW($A$3:$A$18)/($A$3:$A$18<>""),$A$3:$A$18)=J$4)*(LOOKUP(ROW($B$3:$B$18),ROW($B$3:$B$18)/($B$3:$B$18<>""),$B$3:$B$18)=J$5)*($C$3:$C$18=J$6)*($D$2:$G$2=J$7)*($D$3:$G$18))
Copy towards Right
See the attached file
Change A3 as A's and A7 as B's