# vlookup range selection with variable start point

June 19, 2020 at 14:04:43
Specs: Windows10
 Hi guys, just struggling with this and not sure if it can be doneI am trying to set the range on a a VLOOKUPThe problem I have is that the range is variableI would normally set the range as for example E5:G36E5 is know a variable J1 denotes the row number and this is my variable, ranging in value from 10 to 32so I need to set my range as E('value of J1'):G36Is this possible and how do I do it???????Tony See More: vlookup range selection with variable start point

#1 June 20, 2020 at 03:30:23
 Sorry, not sure what your asking, but my best guess is you will need to use an INDIRECT() functionand build your variable range, something like:```E & INDIRECT(J1) : G36 ```I do not have time today, sorry, but will work on this tomorrow.MIKEmessage edited by mmcconaghy

#2 June 21, 2020 at 08:51:26
 Simplest solution is to put both and Column Letter & Row Number together in J1So something like:=VLOOKUP(B1,INDIRECT(J1):G36,2)J1 = E12 or E13 or E30 or what ever starting cell you want.You could also use a Named Range, but I'm still not sure what your doing.MIKEhttp://www.skeptic.com/

#3
June 21, 2020 at 11:23:48
 Hi Mike,Thanks for coming back. I'll try and explain a little clearer=VLOOKUP(B1,D1:G36,2) would be the formula to use However the value of J1 would vary from a value of 8 to 16. This value would determine the row numberTherefore if J1 were to be the value of 11, the formula would be as =VLOOKUP(B1,D11:G36,2)If J1 were to be the value of 16, the formula would be as =VLOOKUP(B1,D16:G36,2)So I'm looking for the VLOOKUP formula to take the value of J1 =VLOOKUP(B1,D(J1):G36,2)Tony

Related Solutions

#4 June 21, 2020 at 14:04:54
 Tony,Using the INDIRECT() function, the only way is to enter both the COLUMN letter & ROW number in J1so J1 would be E10 or E15 or E30 etc.I'll continue to play around with trying to build the table array differently, but WHY are you doing it this way?As best as I can tell there is no speed gain by simply changing the row value.MIKEhttp://www.skeptic.com/

#5 June 21, 2020 at 14:55:45
 Here is a round about way of doing it that may suit your needs.First we are going to use the ADDRESS function to build s suitable address:Somewhere in an out of the way cell like X1 enter the formula:=ADDRESS(J1,5)This translates to: The row value in cell J1 and the 5th Column or the E columnNow the VLOOKUP formula we can do:=VLOOKUP(B1,INDIRECT(X1):G37,2)This will allow you to change the Row number in J1 but the Column will remain as Column E MIKEhttp://www.skeptic.com/

#6 June 23, 2020 at 04:50:10
 This one combines the two previous formula into one, which I should have realized earlier, no need for using cell X1=VLOOKUP(B1,INDIRECT(ADDRESS(J1,5)):G37,2)MIKEhttp://www.skeptic.com/ 