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.

Sorry, for the messy data above. I should have known it would get scattered when I posted it. I'm not sure it matters, but "Apples" is supposed to be in column F.

On Friday, July 12, 2013 7:12:54 PM UTC-7, wrote:
Sorry, for the messy data above. I should have known it would get scattered when I posted it. I'm not sure it matters, but "Apples" is supposed to be in column F.

I did a quick test of the formula I posted and used a drop down in E1 for the item to look up and it worked for column D to lookup E1 value and returned the value in same row from column B.

=INDEX($B$5:$B$17,MATCH(E1,$D$5:$D$17,0))

Regards,
Howard

On Friday, July 12, 2013 6:47:40 PM UTC-7, wrote:
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.

There is a lot of genius floating about this forum, so me thinks this is not the only way.

I'm going with a formula for each column as you mention.

<Or do I have to just do individual formulas to search for a match in each of the columns?

=INDEX(column with data you want 100,500 etc., MATCH(value you are looking for "apple" or cell ref, column which contains this data,0))

=INDEX($A$5:$A$17,MATCH(1088,$D$5:$D$17,0))

Where the 1088 is replaced with "Apple" or more likely a cell reference of the item you want to lookup.

Untested. Good luck.

Regards,
Howard