+ Reply to Thread
Results 1 to 9 of 9

index match for rows and columns

  1. 09-01-2014, 08:40 PM #1
    lejanco
    lejanco is offline
    Forum Contributor
    Join Date
    05-05-2014
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2013
    Posts
    103

    index match for rows and columns

    Hello,

    I am looking for an INDEX Match formula for cell B44:C59, that will pull the corresponding value in the table A:21:S39, for example, the cell value in B:44 should be 1,132,615. But my current formula is not pulling correctly. Thank you for your help. Below is my current formula.

    =INDEX(A21:S39,MATCH(A44,A21:S21,0)*MATCH(B43,A21:A39,0),0)

    Laura
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 09-01-2014, 08:56 PM #2
    gak67
    gak67 is offline
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: index match for rows and columns

    Some clarification please. For cell B44 you want the figure from column B as A44 is Jan-13 and you want the figure from row 23 because B43 is Feb-13?

    Or do you want row 22 because A44 is Jan-13 and column C because B43 is Feb-13?
    Please add reputation by clicking on the * if I have helped.
    Please mark the thread SOLVED if your issue has been resolved.
    Thanks, Glenn.
    Reply With QuoteRegister To Reply
  3. 09-01-2014, 08:57 PM #3
    azumi
    azumi is offline
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,373

    Re: index match for rows and columns

    Change to this:

    =INDEX(A21:S39,MATCH($A44,$A$21:$S$21,0),MATCH(B$43,$A$21:$A$39,0))
    Reply With QuoteRegister To Reply
  4. 09-01-2014, 09:14 PM #4
    lejanco
    lejanco is offline
    Forum Contributor
    Join Date
    05-05-2014
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2013
    Posts
    103

    Re: index match for rows and columns

    Hello,

    Azumi: as an array that is still pull the wrong number (pulling a 0)
    Gak67: the value in B44 should be the corresponding value to A:44 from row 21 and B:43 from column A22:A39.

    Thank you!
    Reply With QuoteRegister To Reply
  5. 09-01-2014, 09:23 PM #5
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,629

    Re: index match for rows and columns 

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Ben Van Johnson
    Reply With QuoteRegister To Reply
  6. 09-01-2014, 09:25 PM #6
    gak67
    gak67 is offline
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: index match for rows and columns

    Try this:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    It is not an array formula - it doesn't need to be.
    Reply With QuoteRegister To Reply
  7. 09-01-2014, 09:41 PM #7
    lejanco
    lejanco is offline
    Forum Contributor
    Join Date
    05-05-2014
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2013
    Posts
    103

    Re: index match for rows and columns

    Thank you!
    Reply With QuoteRegister To Reply
  8. 09-01-2014, 10:11 PM #8
    Vikas_Gautam
    Vikas_Gautam is offline
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Thumbs up Re: index match for rows and columns

    Try this.. Copy Horizontally and Down..
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Check the attached file..
    Attached Files Attached Files
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com
    Excel is not a matter of Experience, its a matter of Application.
    Say Thanks, Click * Add Reputation
    Reply With QuoteRegister To Reply
  9. 09-01-2014, 10:21 PM #9
    lejanco
    lejanco is offline
    Forum Contributor
    Join Date
    05-05-2014
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2013
    Posts
    103

    Re: index match for rows and columns

    Thank you all!
    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. INDEX MATCH with Rows and Columns as reference
    By Locust in forum Excel General
    Replies: 4
    Last Post: 06-11-2014, 02:35 PM
  2. Index and Match with multiple columns/rows
    By fab121 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-18-2012, 02:33 PM
  3. Increment columns in INDEX and MATCH formula - ROWS and COLUMNS
    By nickmangan in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2012, 10:38 AM
  4. Index and Match for both columns and rows
    By sofib09 in forum Excel General
    Replies: 2
    Last Post: 12-08-2010, 03:29 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1