30,000 INDEX AND MATCH, will it slow down my worksheet?

[shophoney]

Hi I've created a inventory worksheet. I've tried to used power query/powerpivot as much as possible. But have no choice it would seem but to use INDEX and MATCH in 30,000 cells. Would that make the sheet really slow?

I need to link product, sales, receiving and transfers all together. But sometimes and item has not been sold but has been received and wont line up in the lsit. So INDEX seems best.

Thoughts?

 

[63falcondude]

I don't think that it would be a problem. Try to limit the ranges in the formula (not whole column references).

Try it and let us know!

 

[FDibbins]

Just to clarify...
you want to put INDEX/MATCH in 30 000 cells?
or you want INDEX/MATCH to reference 30 000 cells?

if th3 1st option, does any of your data remain static (unchanged) after a certain stage or time, such that the formulas for those entries can be copied to values?

 

[shophoney]

Hi sorry, to be more clear.

I have 11 locations, with about 500 rows of possible data and 6 types of transactions.

So I'm INDEXing 11 locations x 500 lines for each transaction type. By 500 products.

So each group of index is a grid of 11 locations (columns), by 500 products(rows), and there will be 6 groups.

Thanks for helping

 

[shophoney]

Also to make each unique formula an array i need to hit CTRL, SHIFT,ENTER.

Is there a faster way to have it do all 30,000 cells? When i select all of them if makes all the formulas the same.

Here is an example of a formula:

=INDEX($CS$15:$DC$499,MATCH($B18&$C18&$D18,$CO18:$CO$499&$CP$15:$CP$499&$CQ$15:$CQ$499,0),MATCH(CV$10,$CS$10:$DC$10,0))

or

=INDEX($FC$15:$FM$499,MATCH($B16&$C16&$D16,$EY16:$EY$499&$EZ$15:$EZ$499&$FA$15:$FA$499,0),MATCH(FC$10,$FC$10:$FM$10,0))

 

[FDibbins]

This will eliminate the need for the ARRAY formula, just enter regularly...
=INDEX($CS$15:$DC$499,MATCH($B18 & $C18 & $D18,index($CO18:$CO$499 & $CP$15:$CP$499 & $CQ$15:$CQ$499,0),0),MATCH(CV$10,$CS$10:$DC$10,0))

Can you show some sample data and what you expect?

 

[shophoney]

Hi. You dont think that 30,000 cell's with formulas will slow it down. I dont want to create all the formulas to find out its to slow. lol

I dont know how to send a screen image?

It should look up in my table how many units on hand, sold...

 

[FDibbins]

Hi. You dont think that 30,000 cell's with formulas will slow it down.

Try it and find out, you can always delete/copy...paste values after

 

[shophoney]

Wow what a time saver. Thanks for helping with this. Cheers.