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]

It should be:
=INDEX('Actual Data'!F$7:F$956,MATCH($C15,'Actual Data'!$A$7:$A$956,0))
and it is not an array formula so does not need to be entered with C+S+E.

 

[Jonmo1]

Just a misplaced )

The 0 is part of the Match

try

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

Does not need CSE

 

[Beetelbug]

Awesome - thanks rorya and jon..

and to get rid of the #NAs that show up when there is no value to return, just incorporate the IF(ISNA... In front of that? or is there an easier way...

 

[Jonmo1]

Now, if this is going to be used on several columns, it would save you MUCH sheet performance to put the match in another available column.

From your formula I'm guessing the formula is in Row 15
So say column Z for example

In Z15 put
=MATCH($C15,'Actual Data'!$A$7:$A$956,0)

Then your index match formla is

=INDEX('Actual Data'!F$7:F$956,$Z15)

Then you can drag that formula to the right to incriment the column F, but it continues to use the Match from Z15.
Therefore the match is only done once, and used many times by the formulas in each column.
That saves TONS of sheet performance.

 

[RoryA]

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

you didn't mention whether you are retrieving multiple values for the same lookup value?

 

[Jonmo1]

Awesome - thanks rorya and jon..

and to get rid of the #NAs that show up when there is no value to return, just incorporate the IF(ISNA... In front of that? or is there an easier way...

Incorporating that with my last post
That match formula is in Z15

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

 

[Beetelbug]

Now, if this is going to be used on several columns, it would save you MUCH sheet performance to put the match in another available column.

From your formula I'm guessing the formula is in Row 15
So say column Z for example

In Z15 put
=MATCH($C15,'Actual Data'!$A$7:$A$956,0)

Then your index match formla is

=INDEX('Actual Data'!F$7:F$956,$Z15)

Then you can drag that formula to the right to incriment the column F, but it continues to use the Match from Z15.
Therefore the match is only done once, and used many times by the formulas in each column.
That saves TONS of sheet performance.

yes the formula is in row 15 HOWEVER, there is a different formula/lookup value on EACH row going down..
So, essentially I could put that formula in column Z for all my rows then, huh..

 

[Jonmo1]

Yes, notice the $ in the match formula..

$Z15

That means in your index formula, the reference remains column Z when dragged Right or Left.
But the reference ROW will incriment as dragged down. To $Z16, $Z17 etc...

So as dragged right, your formula will incriment like

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

Then when dragged down it looks like

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

 

[Beetelbug]

Yes, notice the $ in the match formula..

$Z15

That means in your index formula, the reference remains column Z when dragged Right or Left.
But the reference ROW will incriment as dragged down. To $Z16, $Z17 etc...

I understand that part..

and actually when i copy your formula in column Z as you noted, I get a return value of 1, then in the next row, i get 3, etc...

the return value i'm thinking should be the concatenated text that lives in C15, I would think.. eg. "82 CENTRAL WORK TEAM53001 PAYROLL - REGULAR", because THAT is teh data I am looking up on that Actual Data worksheet.

 

[Jonmo1]

As you drag the MATCH formula down,

In row 15 it looks for a match for C15
In row 16 it looks for a match for C16
in row 17 it looks for a match for C17