Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable VLOOKUP source and target
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable VLOOKUP source and target
=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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable VLOOKUP source and target
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable VLOOKUP source and target
<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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable VLOOKUP source and target
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 -- Vindaloo ------------------------------------------------------------------------ Vindaloo's Profile: http://www.excelforum.com/member.php...o&userid=32634 View this thread: http://www.excelforum.com/showthread...hreadid=531866 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create Array for Data Source | Charts and Charting in Excel | |||
Dynamic source list to auto expand | Excel Discussion (Misc queries) | |||
linked values not displayed unless source file open | Excel Discussion (Misc queries) | |||
From Target to Source | Excel Worksheet Functions | |||
Winn98SE, Excel2000: ODBC query opens the source workbook | Excel Discussion (Misc queries) |