Hi, I have a spreadsheet where I would like to use INDEX/MATCH to search across multiple columns. Is this possible? For instance, in this particular spreadsheet, I am searching for a match for “Apples” and then if a match is found, I want to pull the information in from column A. The problem is that “Apples” could be in column B, E, F, or G. Can that be done with one INDEX/MATCH formula? Or do I have to just do individual formulas to search for a match in each of the columns?

My data is something like this:

ColumnA ColumnB ColumnC ColumnD ColumnE ColumnF ColumnG
100 Oranges Lemons Apricots
500 Limes Apples Nectarines
200 Pears
300 Plums Tangerines
700 Grapes Peaches Bananas

And so far my attempts at a formula that would do this have failed:

=INDEX($A:$G,MATCH(I2,$A:$G,0),1)

Thanks in advance for any information.