+ Reply to Thread
Results 1 to 4 of 4

How to get data at an x and y intersection

  1. #1
    Registered User
    Join Date
    02-06-2014
    Location
    Austin, Texas
    MS-Off Ver
    Excel 2010
    Posts
    48

    How to get data at an x and y intersection

    Is there a way to get the data at the intersection of a column that meets a criteria using Index:Match and a row that meets criteria using Index:Match?

    For instance, if my data is:

    John Jim Kelly
    Sept 0 0 10
    Oct 10 0 0
    Nov 0 10 0


    How can I use an Index:Match combination to get the value at the intersection of Jim and Nov? Keeping in mind that my data is a bit more complicated than this, the table has 20+ rows and 10+ columns.

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

    Re: How to get data at an x and y intersection

    Yes.

    Use a formula configured as =INDEX(Array,MATCH(Monthvalue,Monthcolumn,0),MATCH(Namevalue,Namerow,0))

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

  3. #3
    Registered User
    Join Date
    06-30-2011
    Location
    Toronto
    MS-Off Ver
    Excel 2013
    Posts
    14

    Re: How to get data at an x and y intersection

    Please Login or Register  to view this content.
    Where
    E9:G11 are the values
    C14 = month input
    D9:D11 is the column of months (i.e. sept, oct, nov)
    D14 = name input
    E8:G8 is the row of names (i.e. John, Jim, Kelly)

  4. #4
    Registered User
    Join Date
    02-06-2014
    Location
    Austin, Texas
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: How to get data at an x and y intersection

    Drat. Both of these work, but I've asked the wrong question. What I need is a function that will give me the value in the month column for any rows where the value is "10". For instance:

    John Jim Kelly
    Sept 0 10 10
    Oct 10 0 10
    Nov 10 10 0


    I need a function that will locate the first instance of "10" for Jim and return "Sept" and one the will look for the second instance of "10" and return "Nov".

+ 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: 1
    Last Post: 07-16-2013, 06:41 PM
  2. Finding intersection of two column data
    By SiaS in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-15-2012, 03:29 PM
  3. Charting Data to have intersection at top left
    By ligerdub in forum Excel General
    Replies: 2
    Last Post: 02-21-2012, 04:31 AM
  4. [SOLVED] Match data to find intersection
    By Shannon in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-07-2006, 12:30 PM
  5. Data Intersection
    By Lmajeff1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-14-2005, 11:38 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