+ Reply to Thread
Results 1 to 5 of 5

How to select from a table of values based upon column and row headers

  1. #1
    Registered User
    Join Date
    06-27-2012
    Location
    Leeds, UK
    MS-Off Ver
    Excel 2003
    Posts
    14

    How to select from a table of values based upon column and row headers

    Hi all,

    Im sorry to ask this, as im sure it is answered elsewhere, I cannot think of the best means to word a search to return what im looking for!

    Im trying to write an if statement to select from a table (as shown below) based upon the row and column property.

    So on the example workbook ive added, I want to return the number within the table to be selected from its two properties, The purchase price and the annual mileage. For example, for a purchase price of 13000 and an annual mileage of 17000 I need it to return 0.209 etc.

    I know I could do this with if statements but it seems terribly complex and drawn out and am sure that excel will have a much better solution for this!

    Many Thanks in advance! Peter
    Attached Files Attached Files

  2. #2
    Forum Contributor CheshireCat's Avatar
    Join Date
    10-11-2011
    Location
    Victoria, Canada
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    258

    Re: How to select from a table of values based upon column and row headers

    Assuming a target Purchase price in cell B20 and Annual Mileage in B21, try:

    Please Login or Register  to view this content.
    Note - You will need to adjust the values in range C2:J2 to make them numbers instead of text.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,607

    Re: How to select from a table of values based upon column and row headers

    First of all, get rid of the spaces in those "numbers" in row 2, and put 0 (zero) in C2. Then if you use B20 for the mileage, and B21 for the price, you can put this formula in B22:

    =INDEX(C4:J16,MATCH(B21,A4:A15),MATCH(B20,C2:J2))

    although this will give you the result of 0.1939

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    06-27-2012
    Location
    Leeds, UK
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: How to select from a table of values based upon column and row headers

    Thats perfect! Thanks very much to both of you! Jah Bless

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,607

    Re: How to select from a table of values based upon column and row headers

    You're welcome - thanks for feeding back.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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