Data Extraction within Date Range with 1 Condition

[sureshsonti]

Hello,
I have an excel workbook with enter_data tab sheet which has data in a table that I want to extract or display into invoice sheet tab based on date range and hauler. The date range and haulers fields are in the invoice sheet.

This is the data that I want from enter_data tab. I am trying not to use Pivot Tables. Using the formulas would be dynamic

Date / Ticket / Hauler / Plant_Material_Vendor / Qty / Freight Rate / Amount

Thanks for your help and input.

 

[Chihiro]

I wouldn't recommend formula approach... but here's sample using formula.

In B13:
=INDEX(haulers_data[TICKET],SMALL(IF((haulers_data[DATE]>=$C$1)*(haulers_data[DATE]<=$C$2)*(haulers_data[HAULER]=$B$3),ROW(haulers_data[DATE])-1),ROWS($1:1)))

Confirmed as array (CTRL + SHIFT + ENTER) Copy down.

All other columns are simple Index/Match

Personally, I'd use either Advanced Filter (with small vba), or PowerQuery.

 

[Haz]

Check if this is correct.

 

[sureshsonti]

I wouldn't recommend formula approach... but here's sample using formula.

In B13:
=INDEX(haulers_data[TICKET],SMALL(IF((haulers_data[DATE]>=$C$1)*(haulers_data[DATE]<=$C$2)*(haulers_data[HAULER]=$B$3),ROW(haulers_data[DATE])-1),ROWS($1:1)))

Confirmed as array (CTRL + SHIFT + ENTER) Copy down.

All other columns are simple Index/Match

Personally, I'd use either Advanced Filter (with small vba), or PowerQuery.

Mr. Excel Ninja,
Thanks for your. Now I understand why you recommend Advanced Filer with VBA. Really appreciate your help. Thanks Again

 

[sureshsonti]

Check if this is correct.

Mr. Haz,
Thanks for your help. Yes it is working great. Again really appreciation your help.