Problem Using ADDRESS in VLOOKUP

[shinobi]

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

 

[maabadi]

What about:

=VLOOKUP(CELL("address",INDIRECT("AD"&ROW())),$S$9:$AI$26,COLUMN()-COLUMN($S$9)+1,0)

 

[jasonb75]

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]

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]

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.