Changing VLookups to Index/Match

[cincirollers]

With FPL again changing the format of their data, I've gone ahead and converted all my Vlookups to Index/Match so that I no longer have to worry about the hard-coded offsets in Vlookups. Would you expect this to cause any performance issues (because I got some weird issues when first saving the file)?

[Mystery]

You can do this in Excel?????
I'm missing a trick here. Can anyone fill me in how. Then I can answer the question above - not really seeing the point in changing formula other than (seemingly) a little occasional inconvenience.

[Moist von Lipwig]

I used to think I had a reasonable grasp of excel until I joined this forum.

[cincirollers]

You can do this in Excel?????
I'm missing a trick here. Can anyone fill me in how. Then I can answer the question above - not really seeing the point in changing formula other than (seemingly) a little occasional inconvenience.

It is a relatively minor inconvenience, but I've become more adept at using Match/Index of late and will eventually change most, if not all, my lookups to this. The "hardcoded" offsets in them are a pain when you add or delete columns in the source data and so my formulas have been changed from:

=VLOOKUP(B1,FPL!B:BI,54,FALSE)

to

=INDEX(FPL!$A:$BI,MATCH($B1,FPL!$B:$B,0),MATCH(F$1,FPL!$1:$1,0))

B1 is my "key" value that finds the correct row and F$1 is my "data" value that finds the correct column header (the column headers are recreated in the worksheet using the formulas).

I'm also using the "newish" (new to me anyhow) shortcut to ranges. So instead of using A1:Z500, for example, I'm now using A:Z, and so never have to worry about how many rows there are, or whether formulas accessing the data pick up the newly created rows. The problem with this, I'm pretty sure, IS a degredation in performance as the formula will now look at EVERY row in a worksheet, when maybe it only needs to look at a few.

I think I need to arbritarily put a row number on this to account for any number of rows of data, large enough to always include everything, but ignoring the shortcut method to make the formulas more effecient (if this makes any sense). So for example, instead of using:

MATCH(A2,$B:$B,0)

I would use

MATCH(A2,$B1:$B5000,0)

And I have seen inproved performance on this in other worksheets I've created.

[Rob]

The problem with this, I'm pretty sure, IS a degredation in performance as the formula will now look at EVERY row in a worksheet, when maybe it only needs to look at a few.

I think I need to arbritarily put a row number on this to account for any number of rows of data, large enough to always include everything, but ignoring the shortcut method to make the formulas more effecient (if this makes any sense). So for example, instead of using:

MATCH(A2,$B:$B,0)

I would use

MATCH(A2,$B1:$B5000,0)

From my experience, I would agree with the above.