Distinct List & Matching

Hi All,

I have an invoice database that I am trying to pull information from to auto-generate invoices based on a specific date range.

COLUMN A - Invoice Dates (raw data)
COLUMN B - Invoice Numbers (raw data)

COLUMN D - Invoice Numbers (invoice template)
COLUMN E - Invoice Dates (invoice template)
COLUMN G - Specified Invoice Date Range

The goal is to get invoice numbers data from Column B, which have corresponding date data in Column A, to auto-fill in Column D if invoices fall within the date range in Column G. From there, I would like the dates for Column E to correlate with the invoice number in Column D, based on the dates data in Column A.

________________________________________

Current formula in cells D11:D30 is:

=IFERROR(INDEX($B$2:$B$21,MATCH(0,COUNTIF($D$11:D11,IF(($G$8>=$A$2:$A$21)*($G$6<=$A$2:$A$21),$B$2:$B$21,$D$11)),0)),"")

*please note that cell changes to D12, D13, etc based on row...


Current formula in cells E11:E30 is:

=INDEX($B$2:$B$21,MATCH(D11,$A$2:$A$21,0))

*please note that cell changes to D12, D13, etc based on row...

________________________________________


For some reason the formulas currently in columns D & E are not working properly to generate the desired results.

Any and all help would be greatly appreciated to figure this out quickly!

Originally Posted by AliGW

Try this:

=IFERROR(INDEX($B$2:$B$21,MATCH(0,COUNTIF($D$10:D10,IF(($G$8>=$A$2:$A$21)*($G$6<=$A$2:$A$21),$B$2:$B$21,$D$10)),0)),"")

confirmed with CTRL+SHIFT+ENTER (not just ENTER).

And this:

=INDEX($A$2:$A$21,MATCH(D11,$B$2:$B$21,0))

Hi Ali,

I tried your suggestion, but now the destination cells in Column D are all blank and destination cells in Column E are #N/A

Thoughts?

Originally Posted by JieJenn

Can you use Advanced Filter instead of formula?

I tried this out, but I'm working to make this file as easy/user friendly as possible for my colleagues... few steps as possible. I'll look into it though.

Thank you!!!