-1

Is it possible to perform a MATCH function on using an Array Lookup_Value rather than a single Lookup_Value onto a single item? Therefore searching all the items in the Array Lookup_Value to determine if 1 of them is a match to the Lookup_Array (which would be 1 cell rather than an array)

It has to be all the items in the array Lookup_value matching to the 1 item because it is a Wildcard search (I can't match the items in reverse order)

In the below *Amazon* would MATCH with "Amazon AWS 1" & "Amazon Webservices Invoice"

enter image description here

Mturks83
  • 89
  • 2
  • 9
  • 1
    Are you trying to do something like [this](https://stackoverflow.com/questions/53109551/is-there-an-excel-formula-that-will-search-a-list-and-highlight-text-containing)? – BigBen Sep 09 '21 at 20:53
  • 1
    what would be the desired output given what you have provided? – Scott Craner Sep 09 '21 at 20:53
  • Yes that is great to determine whether it exists. Is there a way to extract the ROW number of the TRUE in the SUMPRODUCT to use in an INDEX formula? Thanks!! – Mturks83 Sep 09 '21 at 21:15
  • If you want multiples returns on a single input, MATCH is not the formula for you. But again I ask, what is the final output you desire. Please [edit] the post to include a visual example using the data you provided. – Scott Craner Sep 09 '21 at 21:36
  • I have edited my post with the final output I would like. Essentially a formula in column B to determine if any of the Wildcard values in Column D match with the item in Column A & return Column E – Mturks83 Sep 09 '21 at 21:47

1 Answers1

0

In B4:

=INDEX(E:E,AGGREGATE(15,7,ROW($D$4:$D$5)/(ISNUMBER(SEARCH($D$4:$D$5,A4)),0))),1))

Then copy down.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81