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

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.

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

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

You're welcome - thanks for feeding back.

Pete