Using Index Match to search multiple columns

**myshadeofglory** · 05-24-2012, 09:52 AM

I work in a machine shop. I have a file that could have text values of "Saw", "Lathe", etc. in one of 15 columns. I want to be able to search for the text value I choose and use an OFFSET function to reference the cell directly to the right (setup and run times). This will allow me to make a chart of all machining centers and SUM the number of hours required for each in a week (creating a production and capacity chart). I know that I can do this with a series of IF,INDEX,MATCH formulas that checks each column for the name of the machine as text to give me the information...

(i.e. =IF(INDEX('Quote Book'!B:B,MATCH('Production Schedule'!B2,'Quote Book'!A:A,0))="SAW",INDEX('Quote Book'!C:C,MATCH('Production Schedule'!B:B,'Quote Book'!A:A,0)),IF(INDEX('Quote Book'!E:E,MATCH('Production Schedule'!B2,'Quote Book'!A:A,0))="SAW",INDEX('Quote Book'!F:F,MATCH('Production Schedule'!B2,'Quote Book'!A:A,0)),"No saw so far..."))

However, the real chart has up to 15 operations and we have more than 10 machines, so you can imagine that this formula would be straining for my processor as we have 300 parts running through the shop at any given time... I've attached a simple representation of the issue and the "Saw" columns have an example of the solution I already know is possible. If you can help me with a different proof of concept I should be able to take it from there.

**NBVC** · 05-24-2012, 10:08 AM

Try:

=SUMPRODUCT(('Quote Book'!$A$3:$A$12=$B2)*('Quote Book'!$B$3:$K$12=C$1),'Quote Book'!$C$3:$L$12)

adjust ranges to suit, but limit the range to max needed, not whole columns as this will reduce efficiency

**myshadeofglory** · 05-24-2012, 11:47 AM

Okay, that seems to work. Thanks very much for taking the time to put that together. I'm not familiar with the SUMPRODUCT function. I just looked up an explanation of the formula and I can't seem to figure out how this is working. Could you help me understand what you did here?

**NBVC** · 05-24-2012, 12:14 PM

Have a read here: Sumproduct

Basically you are "multiplying" arrays of TRUE/FALSE results together.. for every TRUE/TRUE match you add 1, all other combos give 0.

**myshadeofglory** · 05-24-2012, 01:02 PM

That's brilliant. Never crossed my mind to break it into true and false. Thanks so much for the help. Do you know if this type of process consumes more or less resources than an INDEX,MATCH? I use a lot of INDEX,MATCH functions to perform simpler versions of this type of referencing where the data is in one column as opposed to an array of columns. We're beginning to notice that it's slowing down our system. (We're basically using excel as an ERP system, minus inventory maintenance... Not ideal, I know...)

**NBVC** · 05-24-2012, 01:12 PM

Sumproduct is less efficient than INDEX/MATCH. But with INDEX/MATCH you would need to sum 15 of them together.. and if you have duplicates in any of the 15 groups, the Index/Match will only account for the first... the Sumproduct, sums all matches in one foul swoop. You could also use SUMIFS, you will need 15 of those also.... but they are too more efficient than Sumproduct, and they will sum multiples from within any table, unlike INDEX/MATCH.

**myshadeofglory** · 05-24-2012, 01:44 PM

One question... I see that the arrays need to be the same size or the function errors out. Column "A" could actually be infinite (perhaps 1000 on average) while "B"-"K" will stay constant, obviously. Will tricking the system and having each array be, say 1000 cells, slow down the program considerably?

**myshadeofglory** · 05-24-2012, 01:51 PM

One question... I see that the arrays need to be the same size or the function errors out. Column "A" could actually be infinite perhaps (1000 on average) while "B"-"K" will stay constant, obviously. Will tricking the system and having each array be, say 1000 cells, slow down the program considerably?

**NBVC** · 05-24-2012, 03:37 PM

Doesn't each of B to K depend on part number in column A? If not, then maybe we don't have the right logic... and you will need to better explain...

**myshadeofglory** · 05-25-2012, 01:48 PM

I'm sorry, I confused myself. Just because the array isn't square any longer doesn't mean it's not an equal area/number of cells. We will have over 1000 that it's referencing though. Will that slow down the workbook considerably?

**myshadeofglory** · 05-25-2012, 02:07 PM

Here is the document.

**NBVC** · 05-25-2012, 02:12 PM

So, as long as you make all the ranges in the formula the same "height" as column A range, you should be fine....

**myshadeofglory** · 05-30-2012, 09:55 AM

I tried to implement that type of formula on the actual sheet. with 15 operations column clusters of three columns each (45 columns) and the 1500 rows, that works out to be 67500 cells that the formula is multiplying by 67500 cells in the sumproduct, and then is multiplying again by 67500 cells. That amount of data being calculated in all 67500 cells crashed excel on my computer. I'm running a 2.4 GHz processor and 4GB of RAM, so I don't think it's my hardware.

I wrote out the 15 IF's and it's calculating. Seems okay at the moment, but it's only going to get bigger.

**NBVC** · 05-30-2012, 10:18 AM

Not sure if you meant you went with your original IFs with INDEX/MATCH...

If so, you can use SUMIFS instead, which is simpler and efficient... so you would sum 15 SUMIFS as...

=SUMIFS('Quote Book'!$C$3:$C$12,'Quote Book'!$A$3:$A$12,$B2,'Quote Book'!$B$3:$B$12,C$1)+SUMIFS('Quote Book'!$F$3:$F$12,'Quote Book'!$A$3:$A$12,$B2,'Quote Book'!$E$3:$E$12,C$1)+ etc.....

**NBVC** · 05-30-2012, 11:22 AM

Here is another way I thought of... not sure if it improves efficiency much.. need to test it...

=SUMPRODUCT(SUMIFS(INDIRECT({"'Quote Book'!$C:$C","'Quote Book'!$F:F","'Quote Book'!$I:$I"}),'Quote Book'!$A:$A,$B2,INDIRECT({"'Quote Book'!$B:$B","'Quote Book'!$E:$E","'Quote Book'!$H:$H"}),D$1))

this allows you to have one SUMIFS formula, you just need to add references in each set of { }

**NBVC** · 05-30-2012, 11:53 AM

One more thought... if the above last post is feasible, we can simplify the work.

First create some dynamic ranges listing the actual range references for each of the Setup, Run and Equipment ranges.

In the attached sample I created 3 Dynamic ranges...

Columns M:O list the columns in Quote Book to reference for the specific category.

Go to Formula tab, Name Manager, and you will see the 3 named ranges... select one to see the formula for making it a Dynamic named range... i.e. you can add more column references in the lists as you go...

Then the formula in C2 becomes:

=SUMPRODUCT(SUMIFS(INDIRECT("'Quote Book'!"&SURange),'Quote Book'!$A:$A,$B2,INDIRECT("'Quote Book'!"&EquipRange),C$1))

now you won't have to play with the formula at all, just add column references to the columns M:O (make sure you add to each column everytime... i.e. they can't be different vertical sizes).

Using Index Match to search multiple columns

LinkBack

Thread Tools

Rate This Thread

Display

Using Index Match to search multiple columns

Re: Using Index Match to search multiple columns

Re: Using Index Match to search multiple columns

Re: Using Index Match to search multiple columns

Re: Using Index Match to search multiple columns

Re: Using Index Match to search multiple columns

Re: Using Index Match to search multiple columns

Re: Using Index Match to search multiple columns

Re: Using Index Match to search multiple columns

Re: Using Index Match to search multiple columns

Re: Using Index Match to search multiple columns

Re: Using Index Match to search multiple columns

Re: Using Index Match to search multiple columns

Re: Using Index Match to search multiple columns

Re: Using Index Match to search multiple columns

Re: Using Index Match to search multiple columns

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions