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.

 

[RoryA]

The MATCH formula returns the position of the lookup item in the lookup column of your data table. You then use =INDEX(value_column, match_row) to retrieve the actual data. Using your example, in column Z you get 1 from the MATCH formula. In say columns AA, AB etc you use:
=IF(ISNA($Z15),"",INDEX('Actual Data'!C$7:C$956,$Z15))
=IF(ISNA($Z15),"",INDEX('Actual Data'!D$7:D$956,$Z15))
to return the actual data from columns C, D etc.
Does that make sense?

 

[Beetelbug]

You guys (gals) are Excel Gods! (Goddesses!)

Thanks so much -- I only wish I could be so cool.

You rock, thanks!

 

[Beetelbug]

OH GEEZ I spoke too soon..

Ok.. all works well EXCEPT in the case when I have no value to look up.. in the match column (Z), i used the ISNA formula and i get a blank. Fine. But perhaps I should change that to a 0 instead of returning blank..

Reason being, when I do the Index formula (referencing that Z column), for those with no values, I"m getting a #VALUE! return..in my columns.

 

[Jonmo1]

No, you can keep the match formula returning "" when there is no value in C

But change your index formula

from

=IF(ISNA($Z15),"",INDEX('Actual Data'!C$7:C$956,$Z15))

to

=IF(OR(ISNA($Z15),$Z15=""),"",INDEX('Actual Data'!C$7:C$956,$Z15))

 

[Jonmo1]

Actually, since you have the match formula in Z testing for ISNA, you don't need to do it in the index formula...

So you could have it like this

Z15: =IF(ISNA(MATCH(...)),"",MATCH(...))

Then your formula in AA or whatever is

=IF(ISNUMBER($Z15),INDEX(....),"")

It will only do the Index if Z15 holds a number (match returned a value).

 

[Beetelbug]

No, you can keep the match formula returning "" when there is no value in C

But change your index formula

from

=IF(ISNA($Z15),"",INDEX('Actual Data'!C$7:C$956,$Z15))

to

=IF(OR(ISNA($Z15),$Z15=""),"",INDEX('Actual Data'!C$7:C$956,$Z15))

perfect!! And you are VERY right - this is speeding up the processing time unbelievably. Thanks again!

 

[Beetelbug]

It will only do the Index if Z15 holds a number (match returned a value).

I'm having issues because the cells that have no data are showing blank (properly) and properly summing my columns, however, when I add a bunch of those items up individually, it's giving me an ugly #VALUE! because there is technically no number in there.

I tried formatting the cells to be numbers, still nothing.

 

[RoryA]

If you are adding them using +, don't. Use SUM instead:
=SUM(A1,C1,E1)
for example rather than =A1+C1+E1
SUM will ignore text.