Filter in Filter or Query/array for multiple sheets

Question

I'm a long time reader, first time poster.

I'm having a problem showing invoice details for each company id for this sheets.

https://docs.google.com/spreadsheets/d/1iTJ8PR3snsbZKh01-yoXXAYc6jLQpjQ5n5z1wFKTRr8/edit?usp=sharing

I can get it to show invoice numbers for each company id within 1 cell with:

=JOIN(CHAR(10), FILTER(Invoices!B:B,Invoices!A:A=CompanyInfo!A2))

and i can also get it to show invoice details for each invoice numbers in 1 cell with:

=JOIN(CHAR(10), FILTER(InvDetails!B:B,InvDetails!A:A=Invoices!B2))

But i would like something like Filter in Filter

=JOIN(CHAR(10),FILTER(Invoices!B:B,Invoices!A:A=FILTER(InvDetails!B:B,InvDetails!A:A=Invoices!B2))

I think i need some query/array to get invoice details to be shown for each company within 1 cell. or maybe i need an apps-script to populate invDetails with companyID so i just can filter invDetails directly

Welcome to Stack Overflow. Questions should be self-contained. Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. — doubleunary
– doubleunary, Commented Nov 6 at 12:16

Wicket · Accepted Answer · 2025-11-07 17:18:57Z

0

Use map(), like this:

=map(tocol(CompanyInfo!A2:A, 1), lambda(companyId, let(
  invoiceIds, filter(Invoices!B2:B, Invoices!A2:A = companyId),
  data, filter(InvDetails!A2:D, match(InvDetails!A2:A, invoiceIds, 0)),
  join(char(10), byrow(data, lambda(row, join(" ", row))))
)))

The formula will get the results for all companies. Put it in a free column.

See map(), tocol(), let(), filter(), match() and byrow().

edited Nov 7 at 17:18

Wicket

39.4k9 gold badges81 silver badges201 bronze badges

answered Nov 6 at 12:13

doubleunary

21.9k13 gold badges38 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Remmerboy Nov 6 at 12:54

Thank you so much. but what's the "æ" do? from my point (as a dane), it's just a letter from our language

doubleunary Nov 6 at 13:47

It's an undefined identifier that gives an error. The formula uses it to get a null row that won't show in the results, to avoid an initial blank row.

doubleunary Nov 6 at 13:54

Edited the answer to use a simpler formula without reduce().

Collectives™ on Stack Overflow

Filter in Filter or Query/array for multiple sheets

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related