arraysgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup

I can't use FILTER with ARRAYFORMULA in Google Spreadsheet

I realize that there are multiple questions with this title, but I don't understand the answers to them, or it is not directly related to my issue.

I'm trying to avoid manually "dragging" a formula to duplicate it for each row.

The formula looks like this

=FILTER(Companies!A$2:C, Companies!A$2:A="USD", Companies!B$2:B=D2)

and it works as well

But when I apply ARRAYFORMULA it shows the same result for each row

=ArrayFormula(IF(ISBLANK(C2:C),, FILTER(Companies!A$2:C, Companies!A$2:A="USD", Companies!B$2:B=D2:D)))

I've shared the spreadsheet with all data

https://docs.google.com/spreadsheets/d/15m0nz2JWrVTu6fZj22Zdu1wnF3q7OVTA25DEl2xMR_k/edit?usp=sharing

I understand that Filter is an arrayformula but I can't understand how I can get appropriate data from another tab.

Solution

  • try in E2:

    =ARRAYFORMULA(IFNA(VLOOKUP(C2:C&D2:D, 
 {Companies!A2:A&Companies!B2:B, Companies!C2:C}, 2, 0)))

    0

    try in F2:

    =ARRAYFORMULA(IFNA(VLOOKUP(E2:E, 'U Companies'!A2:C, {2,3}, 0)))

    0