+ Reply to Thread
Results 1 to 3 of 3

Vlookup, HLookup, Index, Match, oh my! Help me.

  1. #1
    Registered User
    Join Date
    07-23-2013
    Location
    Seattle, Washington
    MS-Off Ver
    Excel 2013
    Posts
    2

    Question Vlookup, HLookup, Index, Match, oh my! Help me.

    Greetings Excel Guru's,

    Hoping to find an answer here, I have a spreadsheet with a large number of tabs that need to all reference/look up information on another tab.

    Attached data sheet - The first tab (Products 1) includes product IDs (Vertical) and dates (Horizontal). The second tab (Products 2) is similar to the first and the third tab (Totals) has dates every other column (Horizontal) and Product names (Vertically) - this final tab is the one all other tabs need to pull information from. I need the Products 1 and Products 2 sheet to look up the product ID and the date on the Totals sheet to find the count for each product on each date. I realize the easy solution would be to put the product names into the far left column and just have the dates consolidate (no space between) but there is a reason for this mess as the data is copied and pasted into the totals tab daily and products drop off and come back on the list at any given date. It would seem index and matching would be the solution, but I was not able to get it to work.

    As an example of what I want on the first tab would be for B2 I want it to look at the Totals tab and find Product ID vertically and the date horizontally and give me the value in that field. The correct result would be 43.

    Just a few notes: 1. I cant do Vlookup because the values I want on the first tab are on every other column on the second tab. 2. copy/paste the data is a solution, but the attachment is just sample data, the workbook I am using is much bigger.

    Hope this all makes sense.

    -Ryan
    Attached Files Attached Files

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Vlookup, HLookup, Index, Match, oh my! Help me.

    hi ryanl, welcome to the forum. it seems like your order of products in Total sheet is always the same? i.e.
    Apples
    Oranges
    Grapes
    Shoes
    T-Shirt
    Pants

    if it is, then try this in B2:
    =INDEX(INDEX(Totals!$B$2:$H$7,,MATCH(B$1,Totals!$A$1:$G$1,0)),MATCH($A2,Totals!$A$2:$A$7,0))

    if it's not, then:
    =INDEX(INDEX(Totals!$B$2:$H$7,,MATCH(B$1,Totals!$A$1:$G$1,0)),MATCH($A2,INDEX(Totals!$A$2:$H$7,,MATCH(B$1,Totals!$A$1:$H$1,0)),0))

    to hide those errors, you can use IFERROR or in Excel 2013, IFNA:
    =IFERROR(INDEX(INDEX(Totals!$B$2:$H$7,,MATCH(B$1,Totals!$A$1:$G$1,0)),MATCH($A2,Totals!$A$2:$A$7,0)),"")

  3. #3
    Registered User
    Join Date
    07-23-2013
    Location
    Seattle, Washington
    MS-Off Ver
    Excel 2013
    Posts
    2

    Re: Vlookup, HLookup, Index, Match, oh my! Help me.

    Holy Forums Batman! that was the fastest response ever!

    If I keep the data in order on the Totals tab, the formula works beautifully so thanks for that!

    The Total sheet may have changes (different products in different rows). I tried moving the rows around and it threw an error in the. I then tried the second formula and it still gives an error. Am I missing something? the data on the first two tabs will always be in the same order, its the totals that will have changes in the product list order.

    Thanks again for the quick response.

    -Ryan

+ 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. Vlookup/hlookup/match/index?
    By margggggg in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-28-2012, 07:46 PM
  2. [SOLVED] Search in Table vlookup / Hlookup or INDEX - MATCH command
    By dalaie in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 09-13-2012, 07:42 AM
  3. [SOLVED] Vlookup & Hlookup at same time - Why is this Index+Match formula not working
    By fabrice.usa in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-24-2012, 06:46 PM
  4. HLOOKUP and INDEX/MATCH?
    By 01FASTWS6 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-07-2012, 01:27 PM
  5. Hlookup and Index match?
    By geng in forum Excel General
    Replies: 4
    Last Post: 12-05-2010, 11:21 PM

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