Speed up Excel VLOOKUP using IF

[Baesucks]

I need to perform a VLOOKUP on a large set of data to get some information from another worksheet (which is closed). I only need the VLOOKUP performed when the field "date" is the last day of the month, so I only need the VLOOKUP to actually excute once for each 30 or so rows. So I have a formula that uses an IF function..

IF(A1=EOMONTH(A1,0), do the Vlookup..., "")

So, in my spreadsheet that has a row for each day of the year, the VLOOKUP should only get executed 12 times. Yet, when I open the spreadsheet it takes a long time to recalculate the external links (like its running a VLOOKUP for each cell whether or not the IF statement is true or false). If I remove the formula from all of the days that are not the end of the month, opening the spreadsheet and recalculating the external links for those 12 rows just takes a second.

It seems like if the IF statement is true the rest of the statment should be ignored entirely. Is there a trick to make that happen?

I'm trying to not use vba code to do this so the users don't need to deal with enabling macros.

 

[GlennUK]

Excel has to resolve external references, whether they are executed or not. This is taking the time, and not the actual calculations. I'd think about redesigning your spreadsheet ... maybe have a month lookup table, and then reference that in your year sheet.

Cheers, Glenn.

Beauty is in the eye of the beerholder.