SUMIF vs. VLOOKLUP

[Beetelbug]

I've had experience with both - but more recently using SUMIF formulas on large financial models (several spreadsheets in one workbook).

I was wondering if there is a considerable difference in calculating/processing time using either one. For example, will my worksheets be less sluggish if I use vlookups vs. SUMIF formulas?

As an example -- I have 15 worksheets, referencing two "data tabs" using SUMIF formulas, to draw specific data into them.

I'm wondering if I use VLOOKUP formulas instead, if it will make a difference and help speed things along

Thanks in advance.

 

[Jonmo1]

This is like comparing apples to cell phones. They are completely different things.

Vlookup does not do the same thing sumif does. So it's not a valid comparison.

Vlookup will return 1 single value based on a criteria.
Sumif will SUM ALL values that meet a certain criteria.

So no, don't use vlookup intstead of sumif. Because you will most likely get the wrong results.

 

[Beetelbug]

This is like comparing apples to cell phones. They are completely different things.

Vlookup does not do the same thing sumif does. So it's not a valid comparison.

Vlookup will return 1 single value based on a criteria.
Sumif will SUM ALL values that meet a certain criteria.

So no, don't use vlookup intstead of sumif. Because you will most likely get the wrong results.

I appreciate that -- and I understand the difference between the two -- the difference is with SUMIF, I can use the same formula globally across all columns and rows (which up front is easier to copy and use in all my tabs), however, in the end, it has to chug through an awful lot of data to give me my result and slows it down.

My VLOOKUP formulas need to be tweaked/tailored to return the correct values in each specific column (which is not a big deal once I set it up once). So, I do understand how to use each, and have found that the VLOOKUP way seems to be faster.

Thanks again.

 

[Jonmo1]

so ultimately, there is only 1 value that matches the criteria? so then sumif returns the same result as vlookup?

Ok, I can see that then.

realistically, what you should be using then, for simplicity and efficiency is Index\match


Take example vlookup

=Vlookup(A1,OtherSheet!A:B,2,FALSE)

You can get same result with index/match formula below. Though this formula looks more complicated, the more you look at it, the simpler it is. And it's more efficient than vlookup, especially when dragged right across multiple columns.

=INDEX(OtherSheet!B:B,MATCH(A1,OtherSheet!$A:$A,0))

Notice the column A of other sheet is Absolute, so it stays the same when dragged right. But the column B will incriment to C to D etc.. as dragged accross to the right.

Hope that helps..

 

[Beetelbug]

Thanks -- in my experience, INDEX and MATCH makes things sluggish as well -- same concept as SUMIF I feel.. because it looks at ALL the data at once..

While VLOOKUP takes longer to set up on my first sheet -- it's is a quick copy/paste job to the others and *seems* as if it is quicker processing..

 

[Jonmo1]

Actually, index/match is WAY better than vlookup.

1. It does NOT look at the entire set of data. given example formula in last post, it only looks in Column A until it finds the matching criteria value. Then it Goes DIRECTLY to that row # in Column A. It does not read all of column A or B. But then again niether does vlookup. But SUMIF most definately DOES look at the whole column, so it can add all the values that match.

2. Vlookup loads a 2D array - at least 2 columns wide. but as dragged right, to get the 3rd 4th 5th....20th column, it then loads a 20 column Array. HUGE performance hit. Index/match only loads 2 1D (single column) arrays. WAY more efficient.

 

[Jonmo1]

There have been a couple lengthy discussions on the subject of vlookup vs index\match

http://www.mrexcel.com/forum/showthread.php?t=322319
http://www.mrexcel.com/forum/showthread.php?t=339375

Now looking back at reason 1 in previous post 6, I would say that respect vlookup is better than sumif. That it doesn't look through the ENTIRE data range, where sumif Does. However, if your vlookup is covering an array with a bunch of columns, then it's a trade off. Difficult which would be more efficient. However, the Index/Match definately is the more efficient of the 3.

Take this vlookup

=vlookup(a1,othersheet!A:M,13,0)

That must load a 13 column Array A:M into memory.

using index match, it would only have to load 2 columns. A and M (B-L are not loaded)

What is the point of loading an array holding columns A through M, when you really only need column A and Column M, B-L are useless to the vlookup, wasted performance.

 

[Beetelbug]

hmmm thanks Jon.. i'll have to revisit this (and restructure my spreadsheets!). I'm not as familiar with Index/Match so it will take some effort.

Thanks so much!

 

[RoryA]

Are you returning data from multiple columns for the same lookup item? If so, then INDEX and MATCH will be much more efficient because you can store the MATCH part in one cell and refer to that from several INDEX formulas - only one lookup needs to be performed.

 

[Beetelbug]

Is there a problem I wonder with this formula??

=INDEX('Actual Data'!F$7:F$956,MATCH($C15,'Actual Data'!$A$7:$A$956),0)

yes, I did the ctrl-shift-enter to enter the array formula..

It gives me the correct answer in the first cell, but when i copy it down, it seems to give me repeat answers in cells below.. not returning the correct value. Does it have anythign to do with the "0" at the end there? I thougth that was used to return an exact match of what is in cell $C15?