+ Reply to Thread
Results 1 to 9 of 9

Returning Value based on references in row and column headers

  1. #1
    Registered User
    Join Date
    08-17-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    29

    Returning Value based on references in row and column headers

    I am trying to write a formula that will return the value in the cell that is the intersection of a row and column that is referenced.

    Almost like using INDEX but instead of defining the row and column I want the formula to search through the rows and columns and pick the row and column that I am referencing and then return the value where that row and column intersect.

    Any ideas?

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Returning Value based on references in row and column headers

    could upload a small example?

  3. #3
    Registered User
    Join Date
    08-17-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    29

    Re: Returning Value based on references in row and column headers

    Yah here is a quick look at the first attempt

    =INDEX($I$13:$N$36,MATCH(S20,$A$3:$A$17,0),MATCH($S$22,$J$13:$N$13,0))

    I'll make a small work sheet to match.

  4. #4
    Registered User
    Join Date
    08-17-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    29

    Re: Returning Value based on references in row and column headers

    Here is a sample

    Edit...forgot to change the formula in the sample worksheet...
    Attached Files Attached Files
    Last edited by uvebeenwarrened; 10-30-2017 at 03:12 PM.

  5. #5
    Registered User
    Join Date
    08-17-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    29

    Re: Returning Value based on references in row and column headers

    I'd like for L6 to reference J6 and J4 and then return the value that is at that intersection in the array.

    So L6 should ultimately return 29 and then L7 return 64, etc...

  6. #6
    Registered User
    Join Date
    08-17-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    29

    Re: Returning Value based on references in row and column headers

    Here is the updated one...
    Attached Files Attached Files

  7. #7
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Returning Value based on references in row and column headers

    In L6, try this:

    =INDEX(C$6:F$27,MATCH(J$4,B$6:B$27,0),MATCH(J6,C$5:F$5,0))

  8. #8
    Registered User
    Join Date
    08-17-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    29

    Re: Returning Value based on references in row and column headers

    Works perfect thanks!

  9. #9
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Returning Value based on references in row and column headers

    You're welcome.

+ 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. [SOLVED] Returning Column Headers for Max Values with Ties
    By JHansee in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-12-2017, 10:00 AM
  2. [SOLVED] How to create column headers formatted correctly while using formula references
    By Bucco63 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-26-2016, 12:25 PM
  3. Retrieve Column Headers Dynamically - Based on column Values
    By akshaysudhir in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-12-2016, 05:42 AM
  4. Returning non-blank cell column/row headers to new array
    By jjkellog in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-19-2013, 10:45 AM
  5. Create headers in workbook based on cell references.
    By Saggy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-15-2013, 11:04 AM
  6. Returning selected column headers
    By risys82 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-28-2008, 12:46 PM
  7. [SOLVED] Excel - returning column headers in a seperate column
    By ExcelConfused in forum Excel General
    Replies: 1
    Last Post: 03-28-2006, 09:55 AM

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