Using the Results of an If-Then Statement in a True Clause
I am trying to use the value returned later in the equation, but I find it difficult, if not impossible, to do so. This is what my formula looks like:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH({"Application","Account","Machine"}, H5))),
INDEX(Sheet1!N$2:N$7,
MATCH(<term that made the SEARCH function true,Sheet1!M$2:M$7,0)), "")
I am trying to search a cell for a series of words, and if one of those words is found, then use that word in an INDEX-MATCH search. Is there a way to do this or do I need to split my work into two steps?
+3
source to share
1 answer
I think you are after this:
=IFERROR(INDEX(Sheet1!N:N, MATCH(
INDEX({"Application","Account","Machine"},
MATCH(TRUE,ISNUMBER(SEARCH({"Application","Account","Machine"},H5)),0)),
Sheet1!M:M,0)), "")
Note that the inner INDEX/MATCH
one is what you were looking for, that is, it returns from the array the keyword that exists within the sentence H5
.
+4
source to share