Converting VLOOKUP and IF Formulas to Array CSE Formulas

I am running a rather large spreadsheet almost all with formulas of varying complexity. To improve calculation and to prevent user errors overwriting cells I have been converting what formulas I can to array CSE formulas.

I have formula in all columns from C:AZ. They all cover from row 77 to row 652

I management the simple formulas easily enough, but need some help with functions that I haven't been able to work out. I have been researching, but I suspect that the problem is my understanding of array formulas.

for example

Formula:

Please Login or Register  to view this content.

is filled down across G77:G652

I tried highlighting the range, entering

Formula:

Please Login or Register  to view this content.

+ CSE
but got very different results

Similarly I am unable to convert a simple IF() statement

Formula:

Please Login or Register  to view this content.

I have a lot of formulas similar to these ones, and I would really like to upskill in array formulas anyway
It's not vital, but any help would be greatly appreciated

Originally Posted by FDibbins

upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

Hi @FDibbins
I hadn't included a sample file because as I'm only asking how to replace fill down formulas, I didn't think it was worth it. But I'll set one up now.

Originally Posted by FDibbins

1st thing to mote is that ARRAY formulas, if used extensively, and on large amounts of data, can actually slow your file down.

Could you please give more feedback on how/why Array formulas would slow the workbook down?
Other sources said that it tends to speed up, and that storage is reduced as (in my case) 700 formulas get replaced with one.
I'm interested because so far I've reduced the the file size a little and it does seem marginally faster. I was going to do a speed test once I had managed to change a few more formula columns

Sorry it took me a while to get back to this.

Here is an example with a section of my data

It looks a bit messy because of the section that I removed due to broken links, and area that I clear to keep things private.

There is a few table for vlookups in the top 70 rows, and a few cells used as multipliers. These a changed and repopulated via vba.

from rows 77 to 652 is data organised by month, with formulas refering to the 1's and 0's in the left most columns.

I was mostly wanting to understand how to convert formulas such as those in columns J and K. Even if you convince me that this is a bad idea, I still want to understand how, so that I can get better at this.

Thanks and regards,
Truk2

Maybe?

=$H77:$H652*$I$27*IF($B77:$B652=0,VLOOKUP($A77:$A652,$H$6:$N$18, 6),VLOOKUP($A77:A652,$H$6:$N$18, 7))

@FDibbins, Initially I was following advice that array formulas will lead to a performance increase. That has now been debunked in my head. But I still wanted to improve my understanding of how to implement arrays so that I am more prepared in the future. Anything with functions or conditionals is currently a significant block for me when it comes to arrays. I also still want to try a time test after I impement a few more arrays.

@Phucam
Thanks. I'll give that a go
Was it necessary to avoid putting the if statement inside the Vlookup?

@Phucam

That worked perfectly, and now that I look at it, I'm really not sure why it gave me trouble
I implemented the if statement the same way, and that worked too. I really can't understand why it was giving me errors when I first posted this.

I ended up with
J77:J652 {=$H77:$H652*$I$27*VLOOKUP($A77:$A652,$H$6:$N$18,IF($B77:$B652=0, 6,7))}
K77:K652 {=IF(F77:F652 > I77:I652, F77:F652-I77:I652,0)}

I guess I'm learning.

Sorry, thought that I already had