Match index value return all the corresponding line item

Good Afternoon sir.

I have create a summary Table form Raw Data corresponding to branch and Insert drop down list in B1 from Required format sheet.B2,B3,B4 and B5 manual entry.

I need breakup details for the summary table.From Required format sheet B1 to B5 against return all the corresponding line item.

DATA;

NAME INVOICE NO INVOICE DATE VALUE DISPATCH DATE CBB WEIGHT TRANSPORT DOC NO
AFGHANISTAN 7218 04/04/2015 47181.94 04/04/2015 24 300 TC 5891
AFGHANISTAN 7219 04/04/2015 61973.02 04/04/2015 24 300 TC 5891
AFGHANISTAN 7253 04/04/2015 79456.08 04/04/2015 24 300 TC 5891
AFGHANISTAN 7254 04/04/2015 34619.45 04/04/2015 24 300 TC 5891
AFGHANISTAN 7255 04/04/2015 40430.83 04/04/2015 24 300 TC 5891

SUMMARY TABLE:

NAME AFGHANISTAN
DISPATCH DATE 04/04/2015
CBB 24
WEIGHT 300
TRANSPORTER TC

REQUIRED FORMAT:

NAME INVOICE NO INVOICE DATE VALUE
AFGHANISTAN 7218 42098 47181.94
AFGHANISTAN 7219 04/04/2015 61973.02
AFGHANISTAN 7253 04/04/2015 79456.08
AFGHANISTAN 7254 04/04/2015 34619.45
AFGHANISTAN 7255 04/04/2015 40430.83

Please help me.

I would use Pivot Table for easiest view.

thank you so much for your help and suggestion Mr.Zbor sir.

i have apply the following formula in C8='=IFERROR(INDEX(INDEX('RAW DATA'!A$2:A$189,MATCH('REQ FORMAT'!$B$2,'RAW DATA'!$E$2:$E$189,0)):INDEX('RAW DATA'!A$2:A$189,MATCH('REQ FORMAT'!$B$2,'RAW DATA'!$E$2:$E$189)),ROWS(C$8:C8)),"") from required format.

But i have try match multiple range(B1,B2,B3,B4 and B5) value return blanks.Please help me.

attached file for your reference.

Your examples are confusing: you imply that the search is on different columns e.g Invoice date in Example 3, value in Example 4 and Despatch date in Example 5.

How are we to differentiate between an Invoice and Despatch Date as the same date will occur in both columns at some point?

And in Example 3, why are the Antigua and Barbuda entries missing as the have the same Invoice Date?

Mr.shukla.ankur281190:

Thanks you so much for your help sir.Array formula return to the corresponding line item fine.some of the branch return #.

Example:Branch Name:CHAD belongs to 19 line item.but formulas picked last one line item rest of column # and

ANTIGUA AND BARBUDA and AUSTRALIA belongs to 2 line item.but formula return 1 line item.

Non array formula return blanks. file attached please help me.

Request: You have mentioned match range B1 and b2.How do Match Range B1 to B5

Mr.JohnTopley

Sorry sir i forgot remove example file.actual required is required format sheet.

sorry for the inconvenience.please give me your suggestion.

Try

=IFERROR(INDEX('RAW DATA'!$A$2:$I$189,SMALL(IF(('RAW DATA'!$A$2:$A$189=$B$1)*('RAW DATA'!$E$2:$E$189=$B$2),ROW('RAW DATA'!$A$2:$A$189)-ROW('RAW DATA'!$A$2)+1,""),ROWS($A$2:A2)),COLUMNS($A:A)),"")

Enter with Ctrl+Shift +Enter

Match on Country and Despatch Date only

There would be NO matches if other parameters are included (CBB, Weight, Tranporter)

Mr.shukla.ankur281190 thanks a lot to continuous reply my thread formulas working fine way.

Mr.JohnTopley thanks so much for your help to consider my query and explanation about parameter.formals return the value is perfectly.

If you have the answer to your problem could please mark the thread as "Solved".