Index/Match in a vba

[Plukey]

I'm currently using two formulas that works great. Thing is.. I'm combing two columns C & D w/

=C2&"-"D2)

for the search on DISPO! Sheet. Searching through "J" and finding a match on Sheet (Key! returning results from columns 2 & 12 . It doesn't return correct result when the combined =C & D once I ctrl C and just paste values the results are right. but I need it continue the rows when someone adds data. The E2:E is the combined C&D that column is hidden.

=IFERROR(INDEX(Shipout_Dispo_Status.xlsm!COMPDATA,MATCH(1,(DISPO!E2:COLME=$B$4)*(Shipout_Dispo_Status.xlsm!STAN=B6),0),2),"")

=IFERROR(INDEX(Shipout_Dispo_Status.xlsm!COMPDATA,MATCH(1,(DISPO!E2:COLME=$B$4)*(Shipout_Dispo_Status.xlsm!STAN=B6),0),12),"")

The results are on another sheet for a co-worker to simply enter eg.116b-22 and gets the current status from 2nd column & closed date from the 12th
Hope I made since...

 

[Plukey]

Sorry, Its not "J" I meant Column E that's combined from Col C & D.... Column "F" for the 2nd criteria to return results from the 2nd and 12th Col The Search Cell is B4 from another Sheet called Report

& the B6 - b30 is on the report sheet.. I wa mixing two different tasks busy day today.

COMPDATA=Complete Data
CLOME= Col "E" (Combined C & D)
STAN= Col "F"
Returning Col B & L