Parse input and auto-replace

**Alabaster** · 08-25-2009, 07:23 PM

I would like to set up a chart of values like so:

..| X| Y| Z|
------------
1|...|...|...|
2|...|...|...|
3|...|...|...|
4|...|...|...|
5|...|...|...|

And then, in another area of the spreadsheet, I would like to be able to type in values quickly in shorthand based on the chart coordinates ("3z" or "4y") and have Excel auto-replace my input with the value in that box of the chart. Ideally I'd like the auto-replace to be nondestructive (I could view it in the "4x" format again later if I wanted). Is there a way to do this? (also, is there a way to do this with 3 coordinates?)

(The values in the chart don't follow a pattern and the x, y, and z are not variables, just headings).

Thanks

**martindwilson** · 08-25-2009, 07:37 PM

use index /match
see attached

**Alabaster** · 08-25-2009, 07:57 PM

Thank you for the quick and very helpful reply.

Is there a way to replace the text within the same cell? I'd like it to behave somewhat like number formats (if I type "5" in a currency-formatted cell it will automatically display it as "$5.00") -- is there something like an advanced custom number-format that will allow me to type "5z" and automatically format that cell to display the value from the array?

Also, is it possible for the input to be case-insensitive (so I can type 5Z or 5z interchangeably?)

Thanks again