Variable VLOOKUP source and target

**Vindaloo** · 04-11-2006, 08:33 AM

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

Niek Otten · 04-11-2006, 09:10 AM

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

**Vindaloo** · 04-11-2006, 09:33 AM

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

Niek Otten · 04-11-2006, 10:40 AM

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

**Vindaloo** · 04-11-2006, 10:56 AM

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