Problem Using ADDRESS in VLOOKUP

shinobi

Board Regular
Hi

I am trying to use an ADDRESS formula as the first parameter in my VLOOKUP function (see below):

=VLOOKUP(ADDRESS(ROW(),COLUMN(\$S\$30)),\$S\$9:\$AI\$26,COLUMN()-COLUMN(\$S\$9)+1,0)

This doesn't seem to work. When I highlight this section and press F9, it shows me the following result: {"\$S\$33"}. This is the right reference - but I was surprised to see the curly brackets around it - am guessing that this is why it is not working.

Any ideas how to get around this?

Thanks

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

maabadi

Well-known Member
What about:
Excel Formula:
``=VLOOKUP(CELL("address",INDIRECT("AD"&ROW())),\$S\$9:\$AI\$26,COLUMN()-COLUMN(\$S\$9)+1,0)``

jasonb75

Well-known Member
Why are you trying to use Address anyway?
Or what is the purpose of the entire formula for that matter? The entire thing looks like a convoluted mess that could most likely be much simpler.

shinobi

Board Regular
Why are you trying to use Address anyway?
Or what is the purpose of the entire formula for that matter? The entire thing looks like a convoluted mess that could most likely be much simpler.

Context is that I am trying to populate a table by referencing data in a pivot table, which could be of variable size on any given run. I was using a macro to essentially set up a new table each time, the size of which was determined by the size of the pivot table that it would reference - so I needed to insert the VLOOKUP formula into the cells in the table each time it was set up (and many of the parameters would be dynamic).

One thing I didn't realise was that, if you use the Range.Formula = "=VLOOKUP" etc syntax (where Range is an array, rather than a single cell), it actually does behave as if you inserted the formula in the top left cell and then copied across the formula (which helps the reference cells etc update appropriately), which is helpful.

Have worked around this problem now - but still surprised about how ADDRESS was handled in this set up.

Thanks

jasonb75

Well-known Member
but still surprised about how ADDRESS was handled in this set up.
The main point to remember here is that ADDRESS returns a text string, not a range. It would be the same as typing ="A1" into a cell instead of =A1
The double quotes make it meaningless to a formula.

I believe that the original purpose of the ADDRESS function was to show the user where a match for something was found rather than showing the result. A formula equivalent to Ctrl f if you like.

Replies
3
Views
93
Replies
1
Views
41
Replies
4
Views
279
Replies
4
Views
112
Replies
3
Views
107

Threads
1,127,104
Messages
5,622,753
Members
415,926
Latest member
jerrynababa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back