Variable VLOOKUP source and target

=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
>

Thanks Niek, that works fine.

However I don't mean to sound ungrateful but that wasn't quite what I'm after. The letters must always be in column A, and the numbers in column B - your solution has the source in column A and the looked-up value in column B, regardless of whether they are a number or a letter.

Your use of INDEX / MATCH solves the problem of not having the key value in the first column of the range (as required by VLOOKUP) - thanks!

Thanks,
Vindaloo

<your solution has the source in column A and the looked-up
value in column B, regardless of whether they are a number or a
letter.>

That is not correct

--
Kind regards,

Niek Otten


"Vindaloo" <Vindaloo.263qsb_1144762501.9503@excelforum-nospam.com> wrote in message
news:Vindaloo.263qsb_1144762501.9503@excelforum-nospam.com...
>
> Thanks Niek, that works fine.
>
> However I don't mean to sound ungrateful but that wasn't quite what I'm
> after. The letters must always be in column A, and the numbers in
> column B - your solution has the source in column A and the looked-up
> value in column B, regardless of whether they are a number or a
> letter.
>
> Your use of INDEX / MATCH solves the problem of not having the key
> value in the first column of the range (as required by VLOOKUP) -
> thanks!
>
> 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
>

Niek,

The formula you posted works fine, but once it has been overtyped it is lost. What I want is for a manually-entered value in cell A5 to be the source to look up a value for B5, and a manually-entered value in cell B5 to be the source to look up a value for A5.

If the user enters a value in cell A5, but then changes their mind and enters a value in cell B5, your formula in cell A5 no longer exists to look up the value in cell B5 (confused? )

I've got a working solution now with the Worksheet_Change event and it's not as clumsy as I first thought. I'd still be interested in any other solutions though.

Thanks,
Vindaloo