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

1. ## 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!

2. ## 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. ## 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. ## 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. ## Re: How to select from a table of values based upon column and row headers

You're welcome - thanks for feeding back.

Pete

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

#### 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