Why is "if isna vlookup" quicker than "countif"?

Chris Source

I have been trying to find if any data persists across two identical reports, but from two different months.

If the ID of a row exists in the next month, then I call it existing. I can see what has come in new and what is carried forward. All good.

The only way I could think to do this was use

=COUNTIF("column of next month IDs","this months ID")

do this all the way down, then just sum it. This is however immensely slow and locks up Excel, as it can be run between 60k and 100k rows.

A colleague recommended that instead I use

=IF(ISNA(VLOOKUP("this months ID","column of next months ID",1,false)),0,1)

and sum that instead. Now it still takes a while, but is noticeably quicker. Why is this? It seems more steps and calculation.

excelperformanceexcel-formula

Answers

comments powered by Disqus