+ Reply to Thread
Results 1 to 6 of 6

formula for looking up data in various sheets by using sheet names + array

  1. #1
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    644

    formula for looking up data in various sheets by using sheet names + array

    I've attached a workbook illustrating my challenge. In one column I list sheet names. In another column, I have various dates.

    I want to create a formula in a third column which takes (a) the sheet name & (b) an array expressed as $A$1:$M$300 and then performs a VLOOKUP function using (a) and (b) to define the array.
    There are over 2,000 rows in the actual sheet I'm using, plus I've got over 75 worksheets (each with identical structure) with the data I need to pull up in the first sheet.

    I've tried nesting INDIRECT within VLOOKUP, but I'm running into probs with the syntax.

    I prefer not to use named ranges, as I believe I'd need to manually name 75 individual ranges.

    Thanks for your help on this.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Malaysia
    MS-Off Ver
    EXCEL 2007/365
    Posts
    1,095

    Re: formula for looking up data in various sheets by using sheet names + array

    Try

    worksheet name : Sheet1
    Cell E8 formula , Drag down

    HTML Code: 
    =IFERROR(VLOOKUP($C8,INDIRECT($B8&"!B6:G18"),3,),"")

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: formula for looking up data in various sheets by using sheet names + array

    Bear in mind that if ANY of the sheet names contains a space, the formula offered above will not work. It needs a little tweak...

    =IFERROR(VLOOKUP($C8,INDIRECT("'"&$B8&"'!B6:G18"),3,FALSE),"")
    Attached Files Attached Files
    Glenn



  4. #4
    Valued Forum Contributor wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Malaysia
    MS-Off Ver
    EXCEL 2007/365
    Posts
    1,095

    Re: formula for looking up data in various sheets by using sheet names + array

    Glenn Kennedy well done job, wk9128 still lack of consideration, thank you so much

  5. #5
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    644

    Re: formula for looking up data in various sheets by using sheet names + array

    Thanks to both of you for your help on this!

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: formula for looking up data in various sheets by using sheet names + array

    You're welcome & thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Replace present formula names with reference data sheet names list
    By Khaldon in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-05-2021, 09:41 AM
  2. Replies: 2
    Last Post: 12-15-2020, 03:18 PM
  3. getting data from other sheets/sheet names
    By PandaWarrior in forum Excel General
    Replies: 1
    Last Post: 07-23-2020, 04:18 PM
  4. Select multiple sheets(array) if sheet names are from cell value or string
    By marvay in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-11-2018, 11:26 PM
  5. Sum Salaries from separate sheets - Sheet names in a dynamic array
    By perducci in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 06-01-2016, 10:24 PM
  6. [SOLVED] Create an Array which has Sheet Names excluding a Few Sheet Names to be used in a MACRO
    By e4excel in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 11-29-2013, 03:24 PM
  7. Replies: 17
    Last Post: 02-01-2013, 12:20 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1