Find column heading,find row value,then return value from ce
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Find column heading,find row value,then return value from ce

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    the column headings are in row 1.ie d1,e1,f1,g1,....etc.the numbers are in column d2,d3,d4,...etc.For example,if i type a name of a column heading into cell a1 and type a number into a2,how do i get the value(returned in cell a3) where column and row meet.ie. look along row 1 for value (typed in a1)then look down column d for value typed in a2.return value into a3 of the cell
    where both column and row meet.
    the ap

    [ This Message was edited by: explorious on 2002-03-17 20:17 ]

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,807
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-17 15:10, explorious wrote:
    the column headings are in row 1.ie d1,e1,f1,g1,....etc.the numbers are in column d2,d3,d4,...etc.For example,if i type a name of a column heading into cell a1 and type a number into a2,how do i get the value(returned in cell a3) where column and row meet.ie. look along row 1 for value (typed in a1)then look down column d for value typed in a2.return value into a3 of the cell
    where both column and row meet.
    the ap
    Shouldn't the layout be something like:

    row 1 from E1 on houses names and
    column D from D2 on houses numbers?

    Aladin



    [ This Message was edited by: Aladin Akyurek on 2002-03-17 21:53 ]

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-17 15:10, explorious wrote:
    the column headings are in row 1.ie d1,e1,f1,g1,....etc.the numbers are in column d2,d3,d4,...etc.For example,if i type a name of a column heading into cell a1 and type a number into a2,how do i get the value(returned in cell a3) where column and row meet.ie. look along row 1 for value (typed in a1)then look down column d for value typed in a2.return value into a3 of the cell
    where both column and row meet.
    the ap

    [ This Message was edited by: explorious on 2002-03-17 20:17 ]

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    There are many ways to do this and a Pivot Table would probably be best. But try this formula

    =INDEX($D$1:$L$1000,MATCH($A$2,$D$1:$D$1000,0),MATCH($A$1,$D$1:$L$1,0))


    D1:L1000
    Is the table
    D1:D1000
    are the row labels
    D1:L1
    are the column labels
    A2
    is the Column heading to find
    A1
    is the Row heading to find.



  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi Explorious:
    As Dave Hawley said there are many ways you can approach this ... one way would be by using the MATCH and VLOOKUP functions. Using the layout delineated by Dave Hawley above, the formula in cell A3 would be:

    =VLOOKUP(A2,$D$1:$L$1000,MATCH(A1,$D$1:$L$1000),0)

    the MATCH function locates the column number of the selected heading for the value to be matched in cell A1, and then the VLOOKUP function locates the selection for the lookup value in cell A2.

    I hope this is what you are looking for ... please post if it works for you, otherwise explain a little further what you are trying to accomplish!

    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com