Vlookup across multiple worksheets

[leeksleeks]

Hi,

I am trying to perform a vlookup across multiple worksheets but cannot seem to work out how. Does anyone have any suggestions as to how this would be possible? I have a results based worksheet where the lookup is based and then worksheets named from Agen:Worcester. I am wanting the lookup to return the value from the 14th cell if it finds the 'keyword' that is based on the corresponding worksheet.

I personally thought that it would be:

=vlookup(N2,Agen:Worcester!A:R,14,false)

but the result i get is #VALUE!

Any help you can offer would be greatly appeciated.

Cheers

 

[VoG]

I think that you will need VBA for this. See Vlookup. Excel Vlookup Across Excel Worksheets. Custom VLookup Formula/Function

 

[leeksleeks]

Works perfectly. Thank you very much!!!

 

[L. Howard]

This is a small chore working with such a long formula, but it works pretty well.
Make a list of your sheet names and then name that list MySheets.

All three formulas are committed with ctrl + shift + enter. Array Entered

 =VLOOKUP(B1,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'![COLOR=#ff0000]A2:A200[/COLOR]"),N2)>0),0))&"'!A2:R200"),14,0)


=VLOOKUP(B1,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'![COLOR=#ff0000]A2:A200[/COLOR]"),N2)>0),0))&"'!A2: R200"),14,0)&" "&LOOKUP(9.99999999999999E+307,1/COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),$B1),MySheets)


=VLOOKUP(B1,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'![COLOR=#ff0000]A:A[/COLOR]"),N2)>0),0))&"'!A:R"),14,0)

The first formula should return the 14th cell value.

The second formula should return the 14th cell value AND the sheet name. (The formula all on one line of course in the formula bar)

Note in both formulas the range is only 200 rows. If you change the 200, be sure to change ALL the 200's in he formula to the new range.

The third formula is untested where it includes the entire columns.

The RED ranges need to match the lookup array row numbers. They are not part of the lookup but must be equal in rows to suit the formula.


Regards,
Howard

 

[Aladin Akyurek]

Also, a bit shorter:

=VLOOKUP(N2,INDIRECT("'"&LOOKUP(9.99999999999999E+307,
  1/COUNTIF(INDIRECT("'"&SheetList&"'!A2:A100"),N2),
  SheetList)&"'!A2:R100"),14,0)

SheetList, like MySheets in the L. Howard post, refers to range which houses the relevant sheet names.

N2 is the look up value.

A2:R100 is the look up table in each sheet (Adjust the range to suit.).

The result is fetched from the 14th column of a relevant table range the LOOKUP bit of the formula picks out.

 

[L. Howard]

Hi Aladin,

I like the shorter version.

I have it on the same sheet as the first two in my post.

The lookup values across eight sheets are a1 to a1591.

Any lookup value greater than a99 returns N/A. Does not seem to want a100 and up.

I array entered the formula. Any ideas?

Regards,
Howard

Woops, my bad, forgot to expand the range to 200.
Still hard to type here in Mr Excel.


Aside: Is it just me?, I have to use hammer force key strokes to type in the posts. Posting and typing seem gnarly to me.

 

[Aladin Akyurek]

...
Woops, my bad, forgot to expand the range to 200.
Still hard to type here in Mr Excel.


Aside: Is it just me?, I have to use hammer force key strokes to type in the posts. Posting and typing seem gnarly to me.

You must have a vaio. The keyboard feels good at the fingertips, but it makes you sometimes miss a key or two...