Are There Formulas Considered "Outdated"

[mcgonma]

Are there formulas that are considered outdated/antiquated/old that would cause a file to crash or get extremely bogged down when it recalculates?
Specifically, I'm being told that VLOOKUPs and ISERROR(MATCH) are old and modern versions of Excel don't handle them well.
My file has about 38,000 rows and about 52 columns; which means that about 2 million cells have these formula.

 

[Joe4]

VLOOKUP is still actively used, but INDEX/MATCH is more efficient than VLOOKUP.

Based on the number of cells with formulas you have, I don't think your real problem is the formulas themselves, but rather the program you are using.
If you have that many formulas doing matching and lookups, it sounds to me what you really have is a relational database.

People sometimes use Excel as a database, but that is really not what it was designed to do, so it doesn't do it really well. It is a spreadsheet program.
Programs like Access, SQL, MySQL, and Oracle are relational database programs, and were designed for this purpose.
So, your performance should increase significantly if you are able to move this to one of these options.

 

[RoryA]

I'm being told that VLOOKUPs and ISERROR(MATCH) are old and modern versions of Excel don't handle them well.

I don't know who told you that but it is simply not true.

 

[mcgonma]

Thank you, Joe. I thought it sounds ludicrous.
I totally agree that a database would be more efficient. Unfortunately, the data I'm working with comes from my client's HR system and I don't know how to get the data into a format let's me move it into a database and be able to run queries against that. And, my own skills are limited such that I don't know how to use SQL, etc. and I don't really know where to turn.

 

[Joe4]

Specifically, I'm being told that VLOOKUPs and ISERROR(MATCH) are old and modern versions of Excel don't handle them well.

To add to what Rory said and expand upon what I said:
- the newer versions don't handle them any worse than the earlier versions did
- however, there are more efficients ways of doing lookups than using VLOOKUP

The following article is an interesting read that describes alternatives methods, and the advantage of these over VLOOKUPs.
https://www.ablebits.com/office-add...ndex-match-function-vlookup/#match-vs-vlookup

Also, one other thing. If you were using a structure like IF(ISERROR(..., note that the newer versions of Excel have a new function that can make these formulas much shorter.
It is called IFERROR. See: https://exceljet.net/excel-functions/excel-iferror-function
If you were using VLOOKUP in conjunction with IF(ISERROR(, using IFERROR can keep you from having to call the VLOOKUP function twice within the same formula.

 

[Joe4]

I don't know how to get the data into a format let's me move it into a database and be able to run queries against that. And, my own skills are limited such that I don't know how to use SQL, etc. and I don't really know where to turn.

Chances are the format that you have to get it in using Excel will also work for Access.
Access might be the logical option, if you have that installed on your computer.

MySQL is a free database program.

Though I have never used it myself, using MSQUERY in conjunction with Excel may be an option too.
See here for information on that: http://www.exceluser.com/formulas/msquery-excel-relational-data.htm

 

[RoryA]

- however, there are more efficients ways of doing lookups than using VLOOKUP

Well, that depends on your setup. If your data is sorted, VLOOKUP can be incredibly fast.

 

[Johnny C]

No, nothing wrong with them. Joe's suggestion re. Index/Match is about as good as you'll get.

You could write some VBA to only calculate parts of the block, the default F9 calculation does the whole workbook but you can write macros that only calculate the sheet or a range within the sheet.

Excel often isn't the best tool for large amounts of data but I feel your pain, in a lot of companies it's the only option. I've been doing a forecast at my current company, it shouldn't be in Excel, but the corporate rules say no Access, no VBA, SQL server only if there's a customer-related business requirement and it has to be done by IT (even though I could do it).