Index/match across multiple columns?

[fjghny]

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 particularspreadsheet, 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 tosearch 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.

 

[fjghny]

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.

 

[lhkittle]

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

 

[lhkittle]

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

 

[fjghny]

Hi, Andy, thanks for your response. I just looked out your website and it looks like there is a lot of good information there. I think it will be helpful.

 

[Claus Busch]

Hi,

Am Sat, 13 Jul 2013 12:18:28 -0700 (PDT) schrieb (e-mail address removed):

Hi, Claus, thank you for your response. I tried your formula and it worked great on several of my spreadsheets. The only problem I encountered was that a few of the spreadsheets have the same search item in more than one row or column, and that seems to give an incorrect result for those items. For instance, if "Apples" appears in both column B and column F, instead of showing the result for the first instance of "Apples" it comes across, it seems to give the corresponding information for something several or sometimes many rows down from the actual match. Is there any way to modify the formula so that it gives the results for the first match found if there is more than one?

try:
=INDEX($A$1:$A$100,SMALL(IF(B$1:G$100="Apples",ROW($1:$100)),ROW(A1)))
and enter the array formula with CTRL+Shift+Enter
and copy down till you get an error

Regards
Claus B.

 

[fjghny]

Hi, Claus, thank you for your response. I tried your formula with one small modification: I changed ROW(A1) to ROW($A$1) and copied it down and it works great. Thank you so much!

 

[Claus Busch]

Hi,

Am Sat, 13 Jul 2013 12:58:01 -0700 (PDT) schrieb (e-mail address removed):

Hi, Claus, thank you for your response. I tried your formula with one small modification: I changed ROW(A1) to ROW($A$1) and copied it down and it works great. Thank you so much!

row(A1) must be relative because it is the counter for SMALL
Row(A1) = the smallest
Row(A2) = the second smallest.


Regards
Claus B.

 

[fjghny]

Hi, Claus, when I make the reference relative it gives some incorrect results and some #NUM! error messages. That's why I decided to try making it into an absolute reference. I was surprised that it worked with an absolute reference, but it seems to. I'm not sure why. I'm not very good with formulas.

 

[Claus Busch]

Hi,

Am Sat, 13 Jul 2013 14:58:59 -0700 (PDT) schrieb (e-mail address removed):

Hi, Claus, when I make the reference relative it gives some incorrect results and some #NUM! error messages. That's why I decided to try making it into an absolute reference. I was surprised that it worked with an absolute reference, but it seems to. I'm not sure why. I'm not very good with formulas.

change the formula:
=IFERROR(INDEX($A$1:$A$100,SMALL(IF($B$1:$G$100="Apples",ROW($1:$100)),ROW(A1))),"")
and enter with CTRL+Shift+Enter
Now when you copy down you get empty cells if no more entry with Apples
exists.


Regards
Claus B.

 

[fjghny]

Hi, Claus, thanks for the new formula. This one works, too, but again onlyif I make the ROW(A1) into an absolute reference. I don't know if it makes a difference, but in the real spreadsheets I'm working with, I'm not using an individual word like "Apples" in the search, I'm using cell referencesthat go all the way down the column. The actual formula as I just tested it looks like this:

{=IFERROR(INDEX($A$1:$A$100,SMALL(IF($B$1:$G$100=I2,ROW($1:$100)),ROW($A$1))),"")}

And I copy it all the way down the column so that I2 changes to I3, then I4, and so on. But both this and the other formula seem to work great with the absolute references. I have no idea why. Array formulas are beyond me.

 

[Claus Busch]

hi,

Am Sat, 13 Jul 2013 15:26:09 -0700 (PDT) schrieb (e-mail address removed):

Hi, Claus, thanks for the new formula. This one works, too, but again only if I make the ROW(A1) into an absolute reference. I don't know if it makes a difference, but in the real spreadsheets I'm working with, I'm not using an individual word like "Apples" in the search, I'm using cell references that go all the way down the column. The actual formula as I just tested it looks like this:

then something is wrong.
Please look here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "Small"
Rightclick and download it.


Regards
Claus B.

 

[fjghny]

Hi, thanks for the file/formula example. I think I know what the problem might be. I didn't explain my data/formula set up very well. I made a copyof your spreadsheet and changed it to show what my spreadsheet looks like.It's on tab 2 of Small 2.xlsx at the following link:

https://skydrive.live.com/#!/?id=E975728022F16FB8!107&cid=e975728022f16fb8

 

[Claus Busch]

Hi,

Am Sat, 13 Jul 2013 19:32:48 -0700 (PDT) schrieb (e-mail address removed):

Hi, thanks for the file/formula example. I think I know what the problem might be. I didn't explain my data/formula set up very well. I made a copy of your spreadsheet and changed it to show what my spreadsheet looks like. It's on tab 2 of Small 2.xlsx at the following link:

https://skydrive.live.com/#!/?id=E975728022F16FB8!107&cid=e975728022f16fb8

yes, then you will get the first result of all items. But "Apples"
exists 4 times and you get only the first result.
With my suggestion you will get all results for "Apples"


Regards
Claus B.

 

[Claus Busch]

Hi again,

Am Sun, 14 Jul 2013 08:54:33 +0200 schrieb Claus Busch:

yes, then you will get the first result of all items. But "Apples"
exists 4 times and you get only the first result.
With my suggestion you will get all results for "Apples"

if you want to sum all values for "Apples" then try:
=SUMPRODUCT(($B$1:$G$100=I1)*($A$1:$A$100))
and copy down.

Regards
Claus B.

 

[fjghny]

Hi, yes, that's actually what I need for now for this project, so it works well. Thank you again!

 

[fjghny]

Hi, thank you for this new formula. This will definitely work for other things I have to do related to what I am working on now. Thank you so much for all your help!