=IF(ISNUMBER(A5),INDEX(A1:A3,MATCH(A5,B1:B3,0)),VL OOKUP(A5,A1:B3,2,FALSE))

--
Kind regards,

Niek Otten

"Vindaloo" wrote in message
...

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 :)

Many thanks,
Vindaloo


--
Vindaloo
------------------------------------------------------------------------
Vindaloo's Profile: http://www.excelforum.com/member.php...o&userid=32634
View this thread: http://www.excelforum.com/showthread...hreadid=531866