Convert an Array Formula’s Text Results into a Usable Format

by

As stated previously, there is no native function which can do what you want in a single cell. If you absolutely cannot use VBA, then you could use a helper column (can hide the column if preferred) and then have the cell where you want the result simply show the last cell of the helper column.

Example:

Produce Name   Type
Apple          Fruit
Broccoli       Vegetable
Carrot         Vegetable
Orange         Fruit

Say you want a single cell to show all Fruit results. You could use another column to host this formula. You will be hiding the column later, so let’s use one out of the way, like column Z. We also want to easily be able to change what you’re looking for, so we’ll put the condition in cell D2. In cell Z2 and copied down, you would use this formula:

=IF(B2=$D$2,IF(Z1="",A2,Z1&", "&A2),IF(Z1="","",Z1))

That would result in the following:

Produce Name   Type              Search For   (other columns until you get to Z)      
Apple          Fruit             Fruit                                             Apple
Broccoli       Vegetable                                                           Apple
Carrot         Vegetable                                                           Apple
Orange         Fruit                                                               Apple, Orange

Then in wherever you want your result cell, we’ll say D3, simply use this formula to get the last result from your helper column, and then hide the helper column.

=Z5

Which results in the following:

Produce Name   Type              Search For
Apple          Fruit             Fruit
Broccoli       Vegetable         Apple, Orange
Carrot         Vegetable
Orange         Fruit

You could use a dynamic named range instead of simply =Z5 to make sure you’re always getting the last cell in your helper column so that your data can grow or shrink and you’ll still get the correct result. But now you can change the contents of cell D2 from Fruit to be Vegetable and now the result cell will show Broccoli, Carrot. Hopefully something like this can be adapted to your needs.

More Related Contents:

  1. Count Unique values with a condition
  2. Want to concatenate multiple row data in in cell along with its values if present. Emp Id should not come if no data available infromt of it
  3. How to convert to percentage [closed]
  4. Excel Number Separation [closed]
  5. Excel formula checklist in employees
  6. I have an Excel data sheet that I would like to extract data from into a new sheet
  7. Getting unique values in Excel by using formulas only
  8. Return empty cell from formula in Excel
  9. Excel: last character/string match in a string
  10. Referencing value in a closed Excel workbook using INDIRECT?
  11. Simple Pivot Table to Count Unique Values
  12. How can I perform a reverse string search in Excel without using VBA?
  13. Excel UNIQUE Across Columns
  14. Filling any empty cells with the value above
  15. How to create a string or formula containing double quotes in Excel?
  16. Find last used cell in Excel VBA
  17. Remove leading or trailing spaces in an entire column of data
  18. Excel Date to String conversion
  19. Excel: Formulas for converting data among column / row / matrix
  20. Excel: Searching for multiple terms in a cell
  21. Three Dimensional Lookup Using INDEX/MATCH
  22. Optimizing Excel formulas – SUMPRODUCT vs SUMIFS/COUNTIFS
  23. How to list all possible combinations of the values in three columns in excel?
  24. Dynamic chart range using INDIRECT: That function is not valid (despite range highlighted)
  25. How to split texts from dynamic range?
  26. Excel: Search for a list of strings within a particular string using array formulas?
  27. Convert from US to UK format
  28. How to roundup a number to the closest ten?
  29. Can’t pass a range to a lambda using Let and Makearray functions
  30. Unique Rank value for a subgroup within a group

Leave a Comment