Search for single value in multiple excel columns and return first value found-CodePudding

I have an excel sheet that has data in 3 columns. I am trying to write a function that will search each column for a single value. If it finds the value, I want to output the value of the column where the value was found.

I have this formula currently, which does what I want for a single column, but I do not know how to modify it to search additional columns in the same row.

=IF(ISNUMBER(SEARCH("apples",J2)),J2,"")

I tried modifying it to:

=IF(ISNUMBER(SEARCH("apples",I2:J2)),J2,"")

But then I received an excel error saying #SPILL

I want to search columns I2:J2 for the word "apples" and if found, output the value in cell J2

How can I modify my function to do this?

CodePudding user response：

Try this (in rows 1 TO 10 as an example):

=INDEX($J$1:$J$10, MATCH("apples",$I$1:$I$10,0))

CodePudding user response：

XLOOKUP() may be best fit for you. Try-

=XLOOKUP("Apple",I2:I10,J2:J10,"Not Found",0)

In case of partial match use match_mode parameter 2 for wildcard character match like-

=XLOOKUP("*Apple*",I2:I10,J2:J10,"Not Found",2)