Google Sheets, arrayformula only returning value once

Question

How can I make a Google Sheets arrayformula+vlookup(or if statements) return the value more than once? Right now it defaults to returning the value but doesnt do it if its listed again. Currently looks something like this:

=ARRAYFORMULA((IF(ROW(A:A)=1,"Class", 
if(G:G=Sheet3!C:C,"Fruit"," "))))

Column G being the "Type" Column

Type, Class

Apple, Fruit
Apple, N/A
Apple, N/A
Banana, Fruit
Banana, N/A

Link to example sheet

https://docs.google.com/spreadsheets/d/10Z9JhGSQ7oi3hyYUBjcx3kAWo2ASrqovIk5OOCsv4Hk/edit?usp=sharing

As player0 implies, there are a lot of unknowns here, since we can't see your full data. However, I can tell you that what your formula is doing right now is checking each listing in G:G against whatever is in that same row in Sheet3. And that is likely not what you want. I suspect you actually need a VLOOKUP check. Again, echoing player0, if you share a link to your sheet so we can see what you so, you'll get the fastest most accurate help. — Erik Tyler

marikamitsos marikamitsos · Accepted Answer · 2020-07-20T09:03:09

You can use the following formula

={"Class";ArrayFormula(IFERROR(VLOOKUP(B2:B,Sheet2!A2:B,2,0)))}

When Sheet2!A2:B has the values

+------------+-------+
|   Class    | Type  |
+------------+-------+
| Apple      | Fruit |
| Orange     | Fruit |
| Kiwi       | Fruit |
| Watermelon | Fruit |
| Banana     | Fruit |
+------------+-------+

Functions used:

Google Sheets, arrayformula only returning value once

1 Answers