+ Reply to Thread
Results 1 to 7 of 7

Need help with Index Match or possibly array type of match

  1. 03-14-2017, 12:11 PM #1
    chadboehne
    chadboehne is offline
    Registered User
    Join Date
    05-24-2011
    Location
    Louisville, Kentucky
    MS-Off Ver
    Excel 2010
    Posts
    24

    Need help with Index Match or possibly array type of match

    Hi,

    I am given a complex data set each week regarding sales plans and I can't get sales to arrange the data in a simple list so I can do an easy Vlookup or Hlookup. I need to be able to lookup a specific data and then return the type of sale that is being planned for that date so I can slap it in a model I built. The problem is that the data isn't laid out with a typical X and Y axis, it's built in a grid like a calendar. I've attached an example sheet. Please look at the highlighted cells in J and K. I could simply just keep building a new Hlookup for each week, but I don't want to keep making Hlookups each week when a new sheet comes out.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-14-2017, 12:38 PM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Need help with Index Match or possibly array type of match

    In K2, then copied down:

    =IFERROR(IFERROR(IFERROR(IFERROR(HLOOKUP($J2,$A$1:$H$2,2,0)&"", HLOOKUP($J2,$A$12:$H$13,2,0)&""), HLOOKUP($J2,$A$23:$H$24,2,0)&""), HLOOKUP($J2,$A$34:$H$35,2,0)&""), HLOOKUP($J2,$A$45:$H$46,2,0)&"")
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 03-14-2017, 12:45 PM #3
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,147

    Re: Need help with Index Match or possibly array type of match

    Try

    in K2

    =IFERROR(INDEX($A$1:$H$31,SUMPRODUCT(($A$1:$H$32=J2)*(ROW($A$1:$H$32)))+1,SUMPRODUCT(($A$1:$H$32=J2)*(COLUMN($A$1:$H$1)))),"")

    Copy down

    Formula corrected
    Last edited by JohnTopley; 03-14-2017 at 12:49 PM.
    Reply With QuoteRegister To Reply
  4. 03-14-2017, 12:59 PM #4
    chadboehne
    chadboehne is offline
    Registered User
    Join Date
    05-24-2011
    Location
    Louisville, Kentucky
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Need help with Index Match or possibly array type of match

    Hmmm, thanks for the response, but neither seems to be working.
    Reply With QuoteRegister To Reply
  5. 03-14-2017, 01:07 PM #5
    chadboehne
    chadboehne is offline
    Registered User
    Join Date
    05-24-2011
    Location
    Louisville, Kentucky
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Need help with Index Match or possibly array type of match

    Got it! simple formatting error on the date cell. Thanks much!!
    Reply With QuoteRegister To Reply
  6. 03-14-2017, 01:10 PM #6
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Need help with Index Match or possibly array type of match

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
  7. 03-14-2017, 01:16 PM #7
    chadboehne
    chadboehne is offline
    Registered User
    Join Date
    05-24-2011
    Location
    Louisville, Kentucky
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Need help with Index Match or possibly array type of match

    Done and thanks again!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Match-Index in stead of Index-Match lookup Array among Arrays
    By Numnum in forum Excel General
    Replies: 2
    Last Post: 10-15-2015, 02:08 PM
  2. Need an array possibly INDEX/MATCH formula with multiple lookup criteria.
    By TheClaw2323 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-25-2015, 09:59 AM
  3. Vlookup with index/match possibly.
    By Kramxel in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-05-2015, 06:06 AM
  4. Problems with multi-criteria Index/Match. LOOKUP works. Index/Match, SUMPRODUCT,ARRAY fail
    By sfike in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  5. Index and Match with Two Criteria, and date Match Type is Less Than
    By ExcelQuestion in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 11-11-2013, 08:57 AM
  6. [SOLVED] Advanced Match Help (possibly Index Match)
    By dfxryanjr in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-08-2013, 09:13 AM
  7. [SOLVED] Looking for formula index/match-type that returns an array
    By Tom in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-01-2005, 05:06 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1