I can't use FILTER with ARRAYFORMULA in Google Spreadsheet

1
votes

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.

1
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup
whats the desired output? – player0
> I'm trying to avoid manually "dragging" a formula to duplicate it for each row You can duplicate the formula by Ctrl + (arrow) to go to the end of the sheet; then Ctrl + C to copy. Navigate to the destination and Ctrl + V to paste the formula. – Greg Rundlett

1 Answers

1
votes

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