Search code examples
google-sheetsindex-match

Sheets: My function uses commas to separate the INDEX MATCH results, but I don't want a comma for the blanks while still having them on matches

I have a workbook with the 4 sheets for home inventory control. The sheets are: Inventory, Freezer, Fridge and Pantry. I manually update the Freezer, Fridge and Pantry items as they change and the Inventory sheet compiles all three.

On the inventory sheet I have:

Location - This is a drop down menu with 3 options, Freezer, Fridge and Pantry and I have added those designations to the corresponding sheets in column F

Item - This is a function that auto populates all ingredients found in Freezer, Fridge and Pantry =UNIQUE(SORT(TRANSPOSE(SPLIT(CONCATENATE(ARRAYFORMULA(UNIQUE(Freezer!B5:B)&CHAR(9)))&CONCATENATE(ARRAYFORMULA(UNIQUE(Fridge!B5:B)&CHAR(9)))&CONCATENATE(ARRAYFORMULA(UNIQUE(Pantry!B4:B)&CHAR(9))),CHAR(9)))))

Quantity - This is a function that auto sums the amount of each item across all three sheets =SUMIF( Freezer!$B:$B, $D5, Freezer!$C:$C ) + SUMIF( Fridge!$B:$B, $D5, Fridge!$C:$C ) + SUMIF( Pantry!$B:$B, $D5, Pantry!$C:$C )

I am trying to pull the location of each item from the three sheets automatically from column F and I found this function, but am having trouble getting the comma's in this function to only show when there are multiple matches.

=IFERROR(INDEX(Freezer!F:F, MATCH($D6, Freezer!B:B, 0)), "") & "," & IFERROR(INDEX(Fridge!F:F, MATCH($D6, Fridge!B:B, 0)), "") & "," & IFERROR(INDEX(Pantry!F:F, MATCH($D6, Pantry!B:B, 0)), "")

This function should bring up Freezer, Fridge, Pantry (or some combination of the three categories depending on whether they are present in the corresponding sheets), but what I don't want to happen is ,,Pantry because it is only found on the Pantry sheet. How do I stop this from happening?

enter image description here

I may be making this way too hard on myself also so if anyone has a better suggestion on how to do this please let me know. I am not great with Excel or Sheets, I just tend to be pretty okay with looking up how to do things and finding a solution when I want something specific to happen on a sheet, but I can't seem to find an adequate answer for this problem. Sorry if this is actually a super simple fix that I am just not seeing.

I have also tried a Vlookup function: =VLOOKUP(VLOOKUP($D6,Freezer!B5:F1000,5),Fridge!B5:F1000,Pantry!B5:F1000) As well as this Index Match function which said there was no match even though the item was present on the Pantry sheet:

=INDEX(Freezer!B5:G1000,MATCH(D6,Freezer!B5:B1000,0), MATCH(D6,Fridge!B5:B1000,0), MATCH(D6,Pantry!B4:B1000,0))

I also tried this which gave me no returns unless it was in Freezer:

=IF(OR(ISNUMBER(MATCH($D5,Freezer!$B:$B,0)), ISNUMBER(MATCH($D5,Fridge!$B:$B,Pantry!$B:$B,0))), VLOOKUP($D5, Freezer!$B:$F, 5, FALSE), "")
Solution

  • Use textjoin(), like this:

    =textjoin(", ", true, 
  iferror(index(Freezer!F:F, match($D6, Freezer!B:B, 0))), 
  iferror(index(Fridge!F:F, match($D6, Fridge!B:B, 0))), 
  iferror(index(Pantry!F:F, match($D6, Pantry!B:B, 0))) 
)

    See textjoin().