Formula Help

[kaia2001]

I am attempting to use INDEX and MATCH to pull one value 'Material Description' from worksheet 'ZFGI04' based on 2 criteria from columns in worksheet 'SHIP REPORT - Test' (FCID and Product) I continue to get 'N/A' I am able to pull one value by using a based VLOOKUP so I assume it isn't simple syntax erroring. Can anyone help with this and point me in the right direction?

Formula: =INDEX(ZFGI04!G:G,MATCH(1,'Ship Report - Test'!D4 = ZFGI04!F:F)*('Ship Report - Test'!G4 = ZFGI04!B:B))

 

[OGCV1]

Just to check you are entering with Ctrl+Shift+Enter for an array formula?

 

[Aladin Akyurek]

Control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(ZFGI04!$G$2:$G$100,SMALL(IF(ZFGI04!$F$2:$F$100='Ship Report - Test'!D4,IF(ZFGI04!$B$2:$B$100='Ship Report - Test'!G4,ROW(ZFGI04!$G$2:$G$100)-ROW(ZFGI04!$G$2)+1)),ROWS($1:1))),"")