Returning all values that meet one criterion

[Velvet Dan]

Hello,

I am trying to look up and return all invoice #'s that were paid in the year 2021. The invoice #'s and year paid are two separate columns. I would like to return all the applicable invoice #'s in column A in another sheet. I have attempted the problem myself using the INDEX function but it only returns the first matching value. Below is the current formula I'm using. I'm using tables so you will notice I have table references instead of ranges.

=INDEX(InvoiceTable[Invoice '#],MATCH(0,IF(InvoiceTable[Year Paid] = 2021, COUNTIF(InvoiceTable[@[Year Paid]],InvoiceTable[Invoice '#]), ""),0))

Thank you in advance!

Sincerely,

Dan

 

[Fluff]

Hi & welcome to MrExcel.
As long as you are not putting the formula into a table, try

=filter(InvoiceTable[Invoice '#],InvoiceTable[Year Paid] = 2021)

 

[Velvet Dan]

Hi & welcome to MrExcel.
As long as you are not putting the formula into a table, try

=filter(InvoiceTable[Invoice '#],InvoiceTable[Year Paid] = 2021)

Darn, unfortunately, I do need to put it into a table. This will be useful in the future though, thank you. Is there anything else I can try?

 

[Fluff]

How about

=INDEX(InvoiceTable[Invoice '#],AGGREGATE(15,6,(ROW(InvoiceTable[Invoice '#])-ROW(InvoiceTable[#Headers]))/(InvoiceTable[Year Paid]=2021),ROWS(S$9:S9)))

Change the S$9:S9 at the end to reflect the 1st cell that the formula is in.

 

[Velvet Dan]

How about

=INDEX(InvoiceTable[Invoice '#],AGGREGATE(15,6,(ROW(InvoiceTable[Invoice '#])-ROW(InvoiceTable[#Headers]))/(InvoiceTable[Year Paid]=2021),ROWS(S$9:S9)))

Change the S$9:S9 at the end to reflect the 1st cell that the formula is in.

Thank you again for the reply. I tried this and the function only displays the first matching invoice # instead of all of the invoice #'s. Any idea why that is?

 

[Fluff]

Did you drag the formula down as far as needed?

 

[Velvet Dan]

Did you drag the formula down as far as needed?

I just tried it and it populates all the cells with the same invoice #, the first match. I’m not sure why it’s not displaying unique records.

 

[Fluff]

If you want distinct numbers use

=INDEX(InvoiceTable[Invoice '#],AGGREGATE(15,6,(ROW(InvoiceTable[Invoice '#])-ROW(InvoiceTable[#Headers]))/(InvoiceTable[Year Paid]=2021)/(ISNA(MATCH(InvoiceTable[Invoice '#],S$8:S8,0))),1))

Change the S$8:S8 to refer to the directly above where you put the formula

 

[Eric W]

If you're putting a formula in a table, you can't drag it down. The identical formula is replicated in each row. But you can do something like this:

Book1
	A	B	C	D	E	F
1	Something	2021 Invoices			Invoice #	Year Paid
2	a	3			1	2020
3	b	4			2	2020
4	c	6			3	2021
5	d	7			4	2021
6	e				5	2022
7	f				6	2021
8	g				7	2021
9	h
10
Sheet3

Cell Formulas
Range	Formula
B2:B9	B2	=IFERROR(INDEX(FILTER(InvoiceTable[Invoice '#],InvoiceTable[Year Paid]=2021),ROW([@[2021 Invoices]])-ROW(Table2[#Headers])),"")

 

[Velvet Dan]

If you want distinct numbers use

=INDEX(InvoiceTable[Invoice '#],AGGREGATE(15,6,(ROW(InvoiceTable[Invoice '#])-ROW(InvoiceTable[#Headers]))/(InvoiceTable[Year Paid]=2021)/(ISNA(MATCH(InvoiceTable[Invoice '#],S$8:S8,0))),1))

Change the S$8:S8 to refer to the directly above where you put the formula

If you're putting a formula in a table, you can't drag it down. The identical formula is replicated in each row. But you can do something like this:

Book1
	A	B	C	D	E	F
1	Something	2021 Invoices			Invoice #	Year Paid
2	a	3			1	2020
3	b	4			2	2020
4	c	6			3	2021
5	d	7			4	2021
6	e				5	2022
7	f				6	2021
8	g				7	2021
9	h
10
Sheet3

Cell Formulas
Range	Formula
B2:B9	B2	=IFERROR(INDEX(FILTER(InvoiceTable[Invoice '#],InvoiceTable[Year Paid]=2021),ROW([@[2021 Invoices]])-ROW(Table2[#Headers])),"")

This worked, thank you both so much for your help!