0

I'm trying to create a report that will match the "type" of activity with specific activity items using a list of possible activity types. I would like to return those that match in another column/table.

In my example, I should have 7 "type a" and 4 "type b," et cetera, and I'd like to return an array of all items that have a type matching from the "type list."

Would an index match formula work for this to return the text values?

enter image description here

mjl
  • 40
  • 5
  • What would you expect for a result and how? – P.b Oct 17 '22 at 21:11
  • Just to spill text values of the results of any "item" whose "type" matches the values in the "type list." I may have the wrong formula, I'm not sure how to tackle it to solve for my desired result. – mjl Oct 17 '22 at 21:17

1 Answers1

3

Use FILTER:

=FILTER(Table1[item],ISNUMBER(MATCH(Table1[type],E3:E7,0)))
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • How is it that simple?! I've been working on this for...embarrassingly too long. This is perfect, thank you. To confirm that I understand this. The filter takes the values in the "item" range I want to return in the output array and looks up if their "type" matches that from the available "type list"? – mjl Oct 17 '22 at 21:27
  • 2
    Yes, if the corresponding type is found in the list the Match returns a number. If not found in the list it returns an error. So the ISNUMBER then will return TRUE/FALSE as the condition for the FILTER. – Scott Craner Oct 17 '22 at 21:28