+ Reply to Thread
Results 1 to 5 of 5

Item Match between TABS (Index,Match???)

  1. #1
    Registered User
    Join Date
    01-25-2013
    Location
    CT, USA
    MS-Off Ver
    Excel 2013
    Posts
    43

    Item Match between TABS (Index,Match???)

    I am looking to come up with a formula that will match two items on separate tabs and different lines and pull data from one tab to the other. The Item in Column A will not always be set on that line so the formula in Column B cannot be static to that line.(Example would be...whether or not ITEMXC is on line 9 or line 223, I need it to pull the QTY data from TAB2....) So basically I need a formula that I can just copy throughout column B to pull the matching data in TAB2. I have attached a simple version of what I am looking for with descriptions in bold and where I want the formula in Red Bold. I will be doing this across 20 tabs for about 2,000 different items so having the ability to automate everything to appear on one page on the same line will be wonderful. I don't even know if this is possible. Excel 2007

    I currently use this formula to match items that will always be on the same line in TAB1...
    =INDEX(TAB2!B:B,MATCH("ITEM3",TAB2!$A:$A,0)) This formula would be next to ITEM3 in column B on TAB1 but if I reload the data and ITEM1 moves from line 8 to line 16, I would have to copy and paste the formula down to line 16. I am working with 2,000 items that fluctuate where they are on the list so copy and pasting constantly each formula for each line is not worth the time.

    Thanks for any input.
    Attached Files Attached Files

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Item Match between TABS (Index,Match???)

    could a sumif work? =SUMIF('TAB2'!A:A,'TAB1'!A5,'TAB2'!B:B)
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    01-25-2013
    Location
    CT, USA
    MS-Off Ver
    Excel 2013
    Posts
    43

    Re: Item Match between TABS (Index,Match???)

    It seems to work in the sample sheet...Let me see if I can use it across my big spreadsheet. THANKS!

  4. #4
    Registered User
    Join Date
    01-25-2013
    Location
    CT, USA
    MS-Off Ver
    Excel 2013
    Posts
    43

    Re: Item Match between TABS (Index,Match???)

    Seems to work across the board with a bit of tinkering! You are amazing. Thank you again.

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Item Match between TABS (Index,Match???)

    You're welcome, and thanks for adding to my reputation.

+ 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. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  2. Replies: 6
    Last Post: 11-08-2013, 10:29 PM
  3. Replies: 5
    Last Post: 02-29-2012, 08:51 PM
  4. Replies: 9
    Last Post: 05-21-2011, 12:14 AM
  5. Index and match function for same item in the table
    By ronlau123 in forum Excel General
    Replies: 3
    Last Post: 05-15-2011, 02:11 AM

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