ExcelBanter - VLOOKUP

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - VLOOKUP (https://www.excelbanter.com/excel-worksheet-functions/189478-vlookup.html)

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.

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.

--
Cheers,
Shane Devenshire
Microsoft Excel MVP

"Dave" wrote:

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.

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.