i am using named variables and ranges in my workbook. I want to use them in vlookup.
if i do not use named parameters, Vlookup works. If i use named parameters, the function does
not return the correct value.
troubleshooting the case in which i use named parameters,
i clicked on the cell with the vlookup function followed by clicking on the formula task bar. it reveals a range that correctly includes the data portion only, but not the first column which is used to find a match.
However, the correct row in the range IS selected. That means excel actually sees the first invisible
column AND correctly matches the first parameter in Vlookup with the unique value in the first column.
What appears to be failing is the selection process which uses the third Vlookup parameter. No matter what I put into Vlookup for that third parameter, I can't seem to get Vlookup to correctly respond.
to experience what I am going through, see the attached workbook. select the worksheet named
"Dimensions - Windows and doors" the green highlight reveals my use of named variables and ranges with the excel Index function. it works. The cells highlighted in purple are the cells in which i am attempting to use vlookup. it might look like it is working, but try changing the matching variable. Ie: Door15 or Door01 and you will see nothing happens.
The cells highlighted in red use vlookup with no named variables or ranges. it works.
in addition there are a few other odd behaviors the non working vlookup returns a duplicate
value as you can see the value to the right of the cell in which vlookup is located. sometimes it seems the vlookup works with named variables as i experiment with the use of @ to change data type from value to referenced data. Then the formula appears to not respond to a change. i do have automatic formula update turned on.
Any help that can be provided will be appreciated. Alternative approaches are welcomed, but i need
to understand what i am doing wrong with named variables and this case is an opportunity to increase my understanding.
By the way, I can not post my excel workbook. your program highlights the work book and issues a failure message. the workbook is stored in OneDrive !!! In addition, I tried saving a copy to my desktop and it too becomes highlighted and this posting will not take place. Also....a message is issued that suggests i save my message on this forum. How? I don't see a "save" button anywhere. my excel workbook is automatically saved.
Hence i am posting this plea for help without the workbook.
