+ Reply to Thread
Results 1 to 8 of 8

Index MATCH MATCH where I need 2 vlookups and 1 hlookup.

  1. #1
    Registered User
    Join Date
    06-02-2016
    Location
    Kershaw,sc
    MS-Off Ver
    2013
    Posts
    4

    Question Index MATCH MATCH where I need 2 vlookups and 1 hlookup.

    INDEX(G15:T40, MATCH(B8,G15:G40,0) & MATCH(B1,H15:H40,0),MATCH(G4,F15:T15,0)

    My questions is how do I connect the 2 vlookups?is this possible?

  2. #2
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Index MATCH MATCH where I need 2 vlookups and 1 hlookup.

    What is the context?
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  3. #3
    Banned User!
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Index MATCH MATCH where I need 2 vlookups and 1 hlookup.

    Try

    =INDEX(G15:T40, MATCH(TRUE,IF(G15:G40=B8,H15:H40=B1),0)MATCH(G4,F15:T15,0))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

  4. #4
    Registered User
    Join Date
    06-02-2016
    Location
    Kershaw,sc
    MS-Off Ver
    2013
    Posts
    4

    Re: Index MATCH MATCH where I need 2 vlookups and 1 hlookup.

    I am trying to get a quantity for a weekly report that shows how much shipped from one location to another. The to and from locations are in the vertical columns. The horizontal row is by date. I am pulling from another sheet but cleared all that info out to simplify it.


    =INDEX('PLT Projected inbounds MTV SLP'!$G$15:$T$40,MATCH(B9,'PLT Projected inbounds MTV SLP'!$G$15:$G$40,0) & MATCH($B$1,'PLT Projected inbounds MTV SLP'!$H$15:$H$40,0),MATCH(G$4,'PLT Projected inbounds MTV SLP'!F15:T15,0))
    Last edited by zhblack; 06-02-2016 at 03:01 PM.

  5. #5
    Registered User
    Join Date
    06-02-2016
    Location
    Kershaw,sc
    MS-Off Ver
    2013
    Posts
    4

    Re: Index MATCH MATCH where I need 2 vlookups and 1 hlookup.

    =INDEX('PLT Projected inbounds MTV SLP'!$F$15:$T$40,MATCH(TRUE,IF('PLT Projected inbounds MTV SLP'!$G$15:$G$40=B8,'PLT Projected inbounds MTV SLP'!$H$15:$H$40=B1),0)*MATCH(G4:I4,'PLT Projected inbounds MTV SLP'!F15:T15,0))

    This is the whole formula with what you suggested. I get #REF!

    I do confirm but not sure If I do it correctly. Does it matter where the cursor is when I confirm?

  6. #6
    Banned User!
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Index MATCH MATCH where I need 2 vlookups and 1 hlookup.

    I can see an error with the last MATCH in your formula, it should be

    =INDEX('PLT Projected inbounds MTV SLP'!$F$15:$T$40,MATCH(TRUE,IF('PLT Projected inbounds MTV SLP'!$G$15:$G$40=B8,'PLT Projected inbounds MTV SLP'!$H$15:$H$40=B1),0),MATCH(G4,'PLT Projected inbounds MTV SLP'!F15:T15,0))

    The cursor should be active in the cell when you confirm, meaning you should see the formula in the cell, not the result.

  7. #7
    Registered User
    Join Date
    06-02-2016
    Location
    Kershaw,sc
    MS-Off Ver
    2013
    Posts
    4

    Re: Index MATCH MATCH where I need 2 vlookups and 1 hlookup.

    That did it. Thank you.

  8. #8
    Banned User!
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Index MATCH MATCH where I need 2 vlookups and 1 hlookup.

    You're welcome, thanks for the feedback!

    If you need to drag this formula to other rows / columns in a table then you might need to make some slight adjustments to that it copies correctly.

+ 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. Index/Match - Dynamic Index Range with Hlookup
    By WassimJMP in forum Excel General
    Replies: 3
    Last Post: 02-16-2016, 12:24 PM
  2. [SOLVED] Hlookup to index match
    By Justair07 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-04-2016, 09:15 AM
  3. Multiple Vlookups and index, match formula
    By alex_a in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-14-2015, 09:46 PM
  4. [SOLVED] Converting Hlookup to Index Match with variable column index number
    By SimonLock in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-09-2015, 09:14 AM
  5. [SOLVED] Index/match/hlookup?
    By Shadefalcon in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-16-2013, 08:34 AM
  6. Index and Match or Hlookup
    By pauldaddyadams in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-10-2011, 04:37 AM
  7. Hlookup and Match or Index and Match?
    By katja328 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-06-2009, 12:33 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