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

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) : G36I do not have time today, sorry, but will work on this tomorrow.

MIKE

message edited by mmcconaghy

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

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

Tony,

Using the INDIRECT() function, the only way is to enterboththe 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

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

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

Ask Your Question

Weekly Poll

Do you think all electric cars need at least a 250-mile range?

Discuss in The Lounge

Poll History