Iferror small formula

Question

i have customer id in column A.  Column B holds unique invoice numbers 001,002,003,etc.  In Column F1 i have a drop down of customer ids and want a formula in G1 to return all invoices associated with Customer ID.  What is the formula to return all invoices associated with customer id.  I can do it with a vlookup, but it only returns the first match.  I believe it's an, iferror, small formula but i can't get it right.  Many thanks.

Answer 1

Hi,

In cell G1, enter this array formula (Ctrl+Shift+Enter) and copy down

=IFERROR(INDEX($A$1:$B$100,SMALL(IF($A$1:$A$100=$F$1,ROW($A$1:$A$100)),ROW(1:1)),2),"")

Hope this helps.

Answer 2

This one works!  Many thanks after hours of time and dozens of formulas...nuch appreciated

Answer 3

Hi Nancyann9092,

I am an independent advisor. You are right you can't use Vlookup function as it will return the first match only.

I have used the Index with small function to return all the values.

=IFERROR(INDEX($B$2:$B$14,SMALL(IF($A$2:$A$14=$F$1,ROW($A$2:$A$14)-1,""),ROWS(G1:$G$1))),"")

Instead of using enter you need to use CTR+SHIFT+ENTER. I have created a sample spreadsheet for you.

https://1drv.ms/x/s!Ao7eDRV32T8gjOgHVMlLb-3vuX3...

Hope it helps in solving the problem.

Answer 4

Hi

Similar questions (return > 1 match) have been asked and answered thousands of times on the community - and probably others…

With A2:B18 formatted as a Table (not mandatory, you can use a range as well) named tblInvoices:

in F2 (avoid re-calcs):

=COUNTIF(TableInvoices[Cust ID],F1)

in G1:

=IF(ROWS(F$1:F1)<=F$2, INDEX(TableInvoices[Invoice '#],AGGREGATE(15,6,(ROW(TableInvoices[Cust ID])-ROW(TableInvoices[#Headers]))/(TableInvoices[Cust ID]=F$1),ROWS(F$1:F1))),"")

and copy down as necessary

AGGREGATE function; ROW function; ROWS function; INDEX function, COUNTIF function

Hope this helps

Answer 5

Hi Nancy

Please, try this formula in cell G2 and copy down

=IFERROR(INDEX($B$2:$B$21,AGGREGATE(15,3,($A$2:$A$21=$F$2)/($A$2:$A$21=$F$2)*(ROW($A$2:$A$21)-ROW($A$1)),ROW(1:1))),"")

Here a video that will provide you with further help

https://www.youtube.com/watch?v=fDB1Ktyhp3Y&t=429s

Hope my answer will help you

Do let me know