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 done
I am trying to set the range on a a VLOOKUP
The problem I have is that the range is variable
I would normally set the range as for example E5:G36
E5 is know a variable
J1 denotes the row number and this is my variable, ranging in value from 10 to 32
so I need to set my range as E('value of J1'):G36
Is this possible and how do I do it???????

Tony


See More: vlookup range selection with variable start point

Reply ↓  Report •

#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() function
and build your variable range, something like:

E & INDIRECT(J1) : G36

I do not have time today, sorry, but will work on this tomorrow.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#2
June 21, 2020 at 08:51:26
Simplest solution is to put both and Column Letter & Row Number together in J1
So 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.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#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 number
Therefore 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


Reply ↓  Report •

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 J1
so 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.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#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 column

Now 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

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#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)

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#7
June 24, 2020 at 13:01:21
Perfect Mike. Just what I needed

Reply ↓  Report •

Ask Question