VLOOKUP

[Dave]

Hi All,
Could someone please tell me why this formula is unacceptable?
=VLOOKUP(A1,{5,(A1+1);10,(A1+2);20,(A1+3)},2)
Regards - Dave.

 

[ShaneDevenshire]

Hi Dave,

You can't put range references inside manually entered {}. However, you can
do what you want as follows:

=VLOOKUP(A1,{5,1;10,2;20,3}*{1,15},2)

where the entry in cell A1 is 15. Or if you still want a cell reference then

=VLOOKUP(A1,{5,1;10,2;20,3}*A1:B1,2)

Now in this example A1 must contain a 1 and B1 is the variable cell you used
in your original formula. This second formula must be array entered.

 

[Dave]

Hi Shane,
Thanks for the explanation. I wasn't aware that we couldn't put range
references inside manually entered {}. I don't see why we shouldn't be able
to. What were the boffins at Microsoft thinking!?
Regards - Dave.