Variable VLOOKUP source and target

Hi,

Imagine I have a range A1:B3 that contains the values A - 1, B - 2, C - 3. I then enter a letter into cell A5, which is to be looked up in the array and to return the corresponding number into B5 (=VLOOKUP(A5, A1:B3, 2, FALSE).

All very simple so far.

Now what I want to do is to be able to enter a number into B5, and have the corresponding VLOOKUP return the correct letter in A5. This is easily achievable on its own, but the tricky part is that I want both of these options to be available at the same time. The user can either enter a letter or a number, and the corresponding number or letter will be returned.

So far I have thought I can do this as follows:

Copy the range A1:B3 and reverse the column order so that the copied range can be used for the second VLOOKUP.

In the Worksheet Change event, trap changes to A5 or B5, and enter the correct VLOOKUP formula in the corresponding cell.

However doing it this way creates a circular reference - the Change event is repeatedly triggered by the subsequent formula change.

Any ideas? Hopefully I'm making all this far too complicated and there's a really easy solution

Edit: I have just realised that I can put

Application.EnableEvents = False

into the code and it prevents the circular reference happening. However my question about an easier solution still stands

Many thanks,
Vindaloo