Is there any other formula I can use rather than lookup which slows excel

[Craigj66]

Example:
In column c7 I enter a code for example 451, in column do I have the formula =if (c7=0,"",Lookup(c7,x6:y540))
Which looks up the name and returns it to do. I have many of these formula ando it slows excel down very badly, I have tried match and index but they return #NO
and #VALUE. What alternative formula can I use so as not to slow excel down?

[adhawan06]

Hi There...Just need small clarity..Do you have data lying in range X6:y540? If Yes, then what kind of is lying in Column X? (I am assuming it might have the codes there)...Also, if possible please upload you file for clear understanding with expected result.

[Special-K]

Post your file. Your description doesn't look correct to me.

[Craigj66]

Please check if you have received the attachment. Yes there is the code in X and the text in Y and the price in Z

[Special-K]

This may not speed things up but use VLOOKUp instead

in C6
=IF($B6=0,"",VLOOKUP($B6,$S$7:$V$150,COLUMN()-1,FALSE))
copy across to E6

in P6
=IF($B6=0,"",VLOOKUP($B6,$S$7:$V$150,COLUMN()-13,FALSE))
and copy down

[MrShorty]

One of the key principles that I see in attempting to speed up a spreadsheet is find and eliminate duplicated effort. Lookups in particular (whether done with the LOOKUP() or MATCH() or VLOOKUP() function) are processor intensive, so speeding a spreadsheet up often involves reducing the number of lookups.

I notice that, as currently structured, your spreadsheet is performing 4 lookups in each row -- basically the same lookup each time (where is [#incolumn2] in the lookup table). My first thought to speed this up is how to reduce these 4 lookups down to 1 lookup. I know you said that you tried the MATCH and INDEX(), but I am guessing that you kept them nested together as a group as we almost always do. In order to speed this up, I would still use MATCH() and INDEX(), but I would separate the two functions so that the MATCH() function is in a helper column (maybe column R or other column). This helper column performs the lookup once per row, and the function might look something like =MATCH(B6,$T$7:$T$100,1). Then, replace your existing lookup functions in column C, D, E, P with the something like =IINDEX($U$7:$U$100,$R6) (where R6 contains the MATCH() function).

I expanded your spreadsheet to 7000+ rows to test this. The original arrangement took about 5 seconds to calculate. This arrangement was near instantaneous.

[HarryGreenwood]

For Ingredients : =INDEX(T7:T39,MATCH(B6,S7:S39,0))
For Unit Cost : =INDEX(U7:U39,MATCH(C6,T7:T39,0))
For UOM :=INDEX(V7:V39,MATCH(C6,T7:T39,0))

Those 3 formulas draw information out from the right hand side.

Not sure what else you're looking at, but if you need to lookup 7000 files, you need VBA not formulas.

[Jonmo1]

so speeding a spreadsheet up often involves reducing the number of lookups.

I notice that, as currently structured, your spreadsheet is performing 4 lookups in each row -- basically the same lookup each time (where is [#incolumn2] in the lookup table). My first thought to speed this up is how to reduce these 4 lookups down to 1 lookup. I know you said that you tried the MATCH and INDEX(), but I am guessing that you kept them nested together as a group as we almost always do. In order to speed this up, I would still use MATCH() and INDEX(), but I would separate the two functions so that the MATCH() function is in a helper column (maybe column R or other column). This helper column performs the lookup once per row, and the function might look something like =MATCH(B6,$T$7:$T$100,1). Then, replace your existing lookup functions in column C, D, E, P with the something like =IINDEX($U$7:$U$100,$R6) (where R6 contains the MATCH() function).

+1, very good advice.

I also notice that you literally duplicated the same lookup twice.
You have this exact same formula in BOTH D6 and P6.
=IF(B6=0,"",LOOKUP(B6,S7:U50))
That's making excel do exactly the same work twice.

So in P6 you could just put
=D6
or Vice versa.

[Craigj66]

Have tried your match and index formula but I get #N/A in R6 and #VALUE! in C6 when I do, what am I doing wrong?

[Craigj66]

Absolutely fantastic!! tried one, works perfectly, will drag through and see if it speeds up the work sheets.

[HarryGreenwood]

Not sure if you are talking to me, if so, I have tried attaching the file as I've edited it, however the file uploader on this site is easily the worst I've ever used.

I've uploaded it here instead, hope it works.

http://s000.tinyupload.com/index.php...20941000261769

If it doesn't let me know.

Cheers,

EDIT****

I've forgotten to Absolutely Address the ranges in my formulas, if you highlight the ranges (E1:E40) for example and press F4, it will hold the ranges in place for your data table, meaning that when you drag the formulas down, the table range won't adjust with it.

[Craigj66]

Hi Harry,
Have changed all lookup formulas to the index and match formula that I did receive, thank you very much, however the response time for calculation is still running at around 4 seconds, any other idea's you may have to help will be much appreciated.

Regards

Craig

[romperstomper]

Here is your example with MrShorty's suggestions implemented.