Problem Using ADDRESS in VLOOKUP

shinobi

Board Regular
Joined
Oct 4, 2005
Messages
76
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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,567
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
What about:
Excel Formula:
=VLOOKUP(CELL("address",INDIRECT("AD"&ROW())),$S$9:$AI$26,COLUMN()-COLUMN($S$9)+1,0)
 
Solution

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,405
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 4, 2005
Messages
76
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
Joined
Dec 30, 2008
Messages
12,405
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,999
Messages
5,622,114
Members
415,876
Latest member
csibonga2k17

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

Which adblocker are you using?

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
Top