How to create a reference table in MS Excel?

I would like to create a reference table in Excel like this:

enter image description here

So everywhere in my workbook or worksheet I type X111, Excel shows the value of 2400.

How could I do that?

1 Answer

You can create a second sheet and use VLOOKUP to do this.

For example, to get the value alone you can use the formula:

=VLOOKUP("X111", Sheetname!A1:B16, 2)

If you're performing a calculation somewhere, insert this formula as needed.

This isn't as concise or "clean" as specifying variables as you might using VBScript, but hopefully this works for you.