Variable VLOOKUP source and target

[Vindaloo]

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

 

[Niek Otten]

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

 

[Vindaloo]

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]

<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

 

[Vindaloo]

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