Range referencing in VLOOKUP function, string concatenate?

[Russ3Z]

I am attempting to perform a vlookup in an array, located among a
group of 21 other arrays. So, each array has a name beside it, but
the arrays all contain Similar, and sometimes identical data. I first
use a MATCH function to find the name and its location on the
worksheet containing all the arrays. I now desire to embed the match
function in my vlookup along these lines...

=vlookup(AJ4,Jobs!"B"&MATCH(AJ3,Jobs!A2:A2123)+2:Jobs!"W"&MATCH
(AJ3,Jobs!A2:A2123)+100)

where AJ3 contains the name i am initially looking up, and AJ4
contains the value I wish to find in the chosen array on worksheet
Jobs. So if the name is Thomas, and match returns 809, I want Vlookup
to search the range B811:W909

If anyone can show me where I am going wrong it will be most
appreciated.

Thank you.

 

[Pete_UK]

You should be able to do it this way:

=vlookup(AJ4,INDIRECT("Jobs!B"&MATCH(AJ3,Jobs!A$2:A$2123)+2&":W"&MATCH
(AJ3,Jobs!A$2:A$2123)+100),col,0)

where col is the column within B811:W909 that you want to return the
corresponding data from.

Hope this helps.

Pete

 

[Russ3Z]

Excellent, that did the trick. Thank you kindly for the helpful and
timely response.

 

[Pete_UK]

You're welcome, Russ - thanks for feeding back.

Now it's my bedtime.

Pete

 

[Shane Devenshire]

Hi,

A somewhat simplier approach might be something like this:

=VLOOKUP(AJ4,OFFSET(Jobs!A2,MATCH(AJ3,Jobs!A4:A2125),,100,23),3,)

Where 3 is the column argument I have choosen for this example.

You will probably need to adjust some of the numbers a little, but this is
the basic idea. And quite a bit shorter.