-1

I have need to enter a value that can vary. It is the last row in a column. I add this variable cell to A1. The value is $L$9001

I have the following formula: =VLOOKUP($H4,'week ending 04JUL'!$A$1:$L$9001,3,FALSE)

In A1 I have the value $L$9001. This figure may change hence why I need to add it in and can't use a hardcoded value.

In B1 to test I get the correct output I have: =INDIRECT("A1") This returns $L$9001 as expected

When I try: =VLOOKUP($H4,'week ending 04JUL'!$A$1:INDIRECT("A1"),3,FALSE) This returns a #VALUE error.

So I need the value in A1 to replace $L$9001 portion of the formula. Any help is greatly appreciated.

Anthony
  • 1
  • 1

1 Answers1

1

You're almost there, Indirect works slightly different. Try this:

=VLOOKUP($H4,Indirect("'week ending 04JUL'!$A$1:"&A1),3,FALSE)

You put the whole range in the Indirect() field, not just the part you're referring to. Also, note the use of quotes (") starting the Range, wrapping your reference into basically a string that Excel uses.

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • would you be able to explain this a little more or point me in the right direction. I have the following: =LARGE(IF(Calculations!$D$4:$D$9003=$F$2,Calculations!$L$4:$L$9003,""),B5). I cover the range as you mention but I get a ref error for the following =LARGE(IF(INDIRECT("Calculations!$D$4:"&$G$2)=$F$2,INDIRECT("Calculations!$L$4:&"$G$3),""),B5) – Anthony Jul 27 '15 at 19:31
  • G2 and G3 contain the correct values I wish to reference in place of the hardcoded values. – Anthony Jul 27 '15 at 19:33
  • Not sure if it's a typo here in StackOverflow, but your second formula has a misplaced `"` (it's coming AFTER the &, it should come before: `=LARGE(IF(INDIRECT("Calculations!$D$4:"&$G$2)=$F$2,INDIRECT("Calculations!$L$4:"&‌$G$3),""),B5)` – BruceWayne Jul 27 '15 at 19:47