Cell Reference vs. VLookup vs. Named Ranges vs. Index/Match

[ransomedbyfire]

I am starting a project in which I have several sheets with several columns and rows. The man who owns the sheet says that he wants me to be able to reference cells all across the workbook in such a way that he can add, delete, and move columns without messing up the formulas. The person who coded this workbook before I came along to revise it used VLookups (which I, admittedly, am not very familiar with). This has left me with two problems (possibly due to my ignorance). I may be wrong, but it seems like these VLookups did not accomplish the task of getting all the references to stay in tact, unless I am reading them wrong. Secondly, it's also leaving me a little confused since I didn't write the stuff myself and am trying to figure out what's what.

So, when I redesign this man's sheet, I would like to be able to reference cells in formulas in such a way that the references do not get messed up no matter how much things change and in a way that whoever comes along after me can easily figure out what's going on.

In the little bit of research I've done, I've come across references to VLookups, Named Ranges, and Index/Match. Could someone please explain the pros and cons of each method?

What would be the best way to do this? Right now, I'm leaning toward named ranges, simply because of their high level of clarity.

Any thoughts?

Thanks in advance for your help!

 

[Gerald Higgins]

Hi.

It sounds like there's lots of questions here...

First off, it's not an "either/or" situation between VLOOKUPs and Named Ranges, or between INDEX/MATCH and Named Ranges.
You can actually include a Named Range within a VLOOKUP formula, for example like this, for a range called data.

=vlookup(a1,DATA,2,false)

Actually, if you wanted to, you could use a Named Range for every single argument in the formula, like this

=vlookup(REFERENCE,DATA,COLUMN,TRUEFALSE)

I use vlookup alot because it's fairly simple, it usually does what I want, and I understand how it works and some of it's limitations.

With Match / Index, you can duplicate the Vlookup function, but you can also do a whole load of other more interesting things beyond Vlookup.

It might be difficult to set something up so that it continues to work no matter how much people mess around with it.
Is it an option to actually REMOVE some of the options to mess around with it, by applying some level of protection ?

 

[ransomedbyfire]

Thanks, Gerald.

Unfortunately, I don't think there is an option to remove too much of the user's ability to move stuff around, because the user is paying me not to limit this ability. I am still grasping the scope of how this workbook works. (It is rather complex, and I believe, somewhat needlessly so.)