Range referencing in VLOOKUP function, string concatenate?

  • Thread starter Thread starter Russ3Z
  • Start date Start date
R

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.
 
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
 
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.
 
Back
Top