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

--
Kind regards,

Niek Otten

"Vindaloo" <Vindaloo.263o0b_1144758904.6002@excelforum-nospam.com> wrote in message
news:Vindaloo.263o0b_1144758904.6002@excelforum-nospam.com...
>
> 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
>