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/182689-vlookup.html)

If I enter York in cell E2 I want Vlookup to be
=VLOOKUP(E2,York,2).
But if I enter Hereford in cell E2 the Vlookup formula must be
=VLOOKUP(E2,Hereford,2).
So, I need this part of the formula to change depending on what is in cell
E2. If there were only two variations I would use a nested formula? But I
have around 25 possibilities that I need to look up. Is there an easier way?

Equaliser --

The first cell of the VLOOKUP formula is the info you want to look up Inside
the lookup array. So you might set up cell E1 as the info you want to look
up, and cell E2 as the name of the array. Here are the steps:

1) Name the ranges for the lookup tables. Select them, then
InsertNameDefine to give them the names (like 'York' and 'Hereford').
2) Your formula would be
=VLOOKUP(E1,INDIRECT(E2),2)

Worked ok for me.

HTH

"Equaliser" wrote:

If I enter York in cell E2 I want Vlookup to be
=VLOOKUP(E2,York,2).
But if I enter Hereford in cell E2 the Vlookup formula must be
=VLOOKUP(E2,Hereford,2).
So, I need this part of the formula to change depending on what is in cell
E2. If there were only two variations I would use a nested formula? But I
have around 25 possibilities that I need to look up. Is there an easier way?