[[email protected]]

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.

[[email protected]]

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.

[[email protected]]

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

[[email protected]]

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

[[email protected]]

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.



On Saturday, July 13, 2013 7:14:30 AM UTC-4, wrote:
This is one of the areas where Lotus 123 still scores over Excel. Lotus had a function called XINDEX that did what you want. You can use INDEX and MATCH to achieve what you want; you can also use SUMPRODUCT. Here is a link to a demo on my website:



http://www.bygsoftware.com/Excel/fun...sumproduct.htm



HTH

Andy

[Claus Busch]

Hi,

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

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[[email protected]]

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!



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.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

[Claus Busch]

Hi,

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

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[[email protected]]

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.





row(A1) must be relative because it is the counter for SMALL

Row(A1) = the smallest

Row(A2) = the second smallest.





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

[Claus Busch]

Hi,

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

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="A pples",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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[[email protected]]

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:

{=IFERROR(INDEX($A$1:$A$100,SMALL(IF($B$1:$G$100=I 2,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.





change the formula:

=IFERROR(INDEX($A$1:$A$100,SMALL(IF($B$1:$G$100="A pples",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.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

[Claus Busch]

hi,

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

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 he
https://skydrive.live.com/#cid=9378A...121822A3%21326
for the workbook "Small"
Rightclick and download it.


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[[email protected]]

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=E97...75728022f16fb8






then something is wrong.

Please look he

https://skydrive.live.com/#cid=9378A...121822A3%21326

for the workbook "Small"

Rightclick and download it.





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

[Claus Busch]

Hi,

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

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=E97...75728022f16fb8

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[[email protected]]

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



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.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

[[email protected]]

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! :)




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.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

[[email protected]]

Hi there,

I wonder if someone can help -- I'm having a similar problem with my workbook and have gone through the different suggestions in this thread to try and resolve, but to no avail! It may be that I'm doing something wrong, but I'd be grateful if someone can point me in the right direction.

In Column C of Sheet 1, I have a list of unique codes (consisting of letters and numbers)
I'd like to search for each code in Sheet 2, Columns B to P; where there is a match, I'd like this to return the result in Column A.

Does anyone have any suggestions?

Thanks!

On Sunday, July 14, 2013 8:52:36 PM UTC+1, wrote:
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! :)




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.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

[Claus Busch]

hi,

Am Tue, 25 Nov 2014 05:45:40 -0800 (PST) schrieb
:

In Column C of Sheet 1, I have a list of unique codes (consisting of letters and numbers)
I'd like to search for each code in Sheet 2, Columns B to P; where there is a match, I'd like this to return the result in Column A.

try:

Sub Test()
Dim LRow As Long, LRow2 As Long, i As Long
Dim varCheck As Variant
Dim myStr As String, FirstAddress As String
Dim rngSearch As Range, c As Range

With Sheets("Sheet1")
LRow = .Cells(Rows.Count, "C").End(xlUp).Row
varCheck = .Range("C1:C" & LRow)
LRow2 = Sheets("Sheet2").UsedRange.Rows.Count
For i = LBound(varCheck) To UBound(varCheck)
myStr = ""
Set c = Sheets("Sheet2").Range("B1:P" & LRow2) _
.Find(varCheck(i, 1), LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
myStr = myStr & c.Address(0, 0) & ", "
Set c = Sheets("Sheet2").Range("B1:P" & LRow2).FindNext(c)
Loop While Not c Is Nothing And c.Address < FirstAddress
End If
If Len(myStr) 0 Then
.Cells(i, 1) = Left(myStr, Len(myStr) - 2)
End If
Next
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[[email protected]]

Thanks Claus.

There isn't an easier way to do this is there...?

On Tuesday, November 25, 2014 2:27:13 PM UTC, Claus Busch wrote:
hi,

Am Tue, 25 Nov 2014 05:45:40 -0800 (PST) schrieb
:

In Column C of Sheet 1, I have a list of unique codes (consisting of letters and numbers)
I'd like to search for each code in Sheet 2, Columns B to P; where there is a match, I'd like this to return the result in Column A.

try:

Sub Test()
Dim LRow As Long, LRow2 As Long, i As Long
Dim varCheck As Variant
Dim myStr As String, FirstAddress As String
Dim rngSearch As Range, c As Range

With Sheets("Sheet1")
LRow = .Cells(Rows.Count, "C").End(xlUp).Row
varCheck = .Range("C1:C" & LRow)
LRow2 = Sheets("Sheet2").UsedRange.Rows.Count
For i = LBound(varCheck) To UBound(varCheck)
myStr = ""
Set c = Sheets("Sheet2").Range("B1:P" & LRow2) _
.Find(varCheck(i, 1), LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
myStr = myStr & c.Address(0, 0) & ", "
Set c = Sheets("Sheet2").Range("B1:P" & LRow2).FindNext(c)
Loop While Not c Is Nothing And c.Address < FirstAddress
End If
If Len(myStr) 0 Then
.Cells(i, 1) = Left(myStr, Len(myStr) - 2)
End If
Next
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional