+ Reply to Thread
Results 1 to 7 of 7

Index, Match, and Vlookup across multiple worksheets using multiple entries

  1. #1
    Registered User
    Join Date
    05-23-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    42

    Index, Match, and Vlookup across multiple worksheets using multiple entries

    Hey guys,

    I am trying to create a worksheet that pulls information from other worksheets to create a calculation guide for products my company sells. I am having difficulty using the Index and Match functions when I go across worksheets. For example, I want to pull the price corresponding to the item (card holder) and the size of the product (3x5). Here is my current formula:

    =INDEX('Two Sided Top Open'!$D$2:$D$3,MATCH('Calculation Sheet'!A3&'Calculation Sheet'!B3,'Two Sided Top Open'!$A$2:$A$3&'Two Sided Top Open'!$B$2:$B$3,0))

    Even after pressing ctrl, shift, enter, I get a N/A value. Do you guys know how I can resolve this? Should I be using Vlookup instead?

    I have attached the document that I am trying to fix. The problem is under the calculation worksheet. Any help would be much appreciated!
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,868

    Re: Index, Match, and Vlookup across multiple worksheets using multiple entries

    Hi and welcome to the forum

    Before I look at coming up with something for you, 1 quick question...

    Why do you have seperate sheets for your data? It's almost alwats better to have all data on 1 sheet (kinda like a data base) and then reference that for other summaries/extracts etc?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    05-23-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Index, Match, and Vlookup across multiple worksheets using multiple entries

    Thanks for the reply FDibbins! The reason for the multiple sheets is becuase I am testing out how to do this equation before doing in on all our other products which are on 50 different sheets. Is there any other way to compute it, without combining it all on one sheet?

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,868

    Re: Index, Match, and Vlookup across multiple worksheets using multiple entries

    I have been working on it, and if you can somehow have your sheet names included in your product name, you can use this regular non-array formula....
    =INDEX(INDIRECT("'"&LEFT(A2,SEARCH(" ",A2,1)-1)&"'!$A:$D"),MATCH($A2,INDIRECT("'"&LEFT(A2,SEARCH(" ",A2,1)-1)&"'!$A:$a"),0),MATCH(B$1,$A$1:$E$1,0))

    I changed your sheet name from Three sided to 3-sided

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,868

    Re: Index, Match, and Vlookup across multiple worksheets using multiple entries

    duplicate post
    Last edited by FDibbins; 05-23-2013 at 08:32 PM.

  6. #6
    Registered User
    Join Date
    05-23-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Index, Match, and Vlookup across multiple worksheets using multiple entries

    Wow thanks! I will definatelu give this a try, and I think I am going to put them all on the same worksheet, simply because it would be less messy. Thanks for all your help!

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,868

    Re: Index, Match, and Vlookup across multiple worksheets using multiple entries

    All data on 1 sheet is a great step in the right direction

    let me know how you make out please?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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