Need to find any point within a 4R by 9C table of data between listed values

[Bud Wilkinsonn]

Hi All,
I am new here but have a reasonable understanding of Excel formulas and some VB / Macro work. I am not an expert by any means and I need help with the following.

I have a table that lists tire spring rates at tested PSI and Camber values from the manufacture.
The table is 4 rows with PSI values for Row headders and 9 columns with Camber degree values for Column headers.
I need to be able to input any Camber value in the range of the column headers including those between listed values and any Tire PSI at all including those between listed values and be able to get an accurate tire spring rate to be displayed as a number to be used in other caluclations.

I have made a pretty eleborate Forecast routine and PSI calculation to achieve my goal but there must be a better way to extract data that is right in front of me in a table.
I have attached my working example but would love a VB Macro to do a lot of this in the background for me or find out there is a better formula stratagy to use. This is not the only table I may have to do this with. In my real workbook many named cells and ranges are used.

Thanks in advance for any help.
Bud

[FDibbins]

assuming your data is in A1:J5, and you have the PSI in K1 and the Camber in L1...

=index(A1:J5,Match(K1,A1:A5,0),match(L1,A1:J1,0))

[Bud Wilkinsonn]

assuming your data is in A1:J5, and you have the PSI in K1 and the Camber in L1...

=index(A1:J5,Match(K1,A1:A5,0),match(L1,A1:J1,0))

FDibbins,
Thanks for this. This is a very elegant way of finding the listed data points in the listed ranges and will be useful to me in the future as well but I needed to be able to project and retrieve the data from ranges between those listed based on the data and ranges that are listed in the manor that tigeravatar's solution provides.

[MarvinP]

Hi Bud,

You didn't supply a sample PSI vs CAM table so I made one up. Here is a double approximation method to find values in between others in a table. See if you can see what I've done in the attached. If you can't then attach your real table and let us have a go at it.

[Bud Wilkinsonn]

Hi Bud,

You didn't supply a sample PSI vs CAM table so I made one up. Here is a double approximation method to find values in between others in a table. See if you can see what I've done in the attached. If you can't then attach your real table and let us have a go at it.

MarvinP,
Thanks for this. This routine also works very well with my table plugged in to place and predicts all the data points between all the listed ones for both inputs and results from the table.

Thanks all

[tigeravatar]

Sorry, didn't realize there was a duplicate thread. I posted my response based off of your sample workbook in the duplicate thread
http://www.excelforum.com/excel-form...75#post3074975

[Bud Wilkinsonn]

Sorry, didn't realize there was a duplicate thread. I posted my response based off of your sample workbook in the duplicate thread
http://www.excelforum.com/excel-form...75#post3074975

I didn't relize I had made a duplicate post either. I had some login trouble early on.

tigeravatar,
Thanks a ton for this formula. There are many elements within it I have not used before so I will be disecting it a bit to help understand what is going on. As I said this may not be the only table that I need to do this with in the future.
Pasted into cell G2 of my example worksheet, this works great. It is very close to what I needed.

=FORECAST(D1,INDEX(--TRIM(MID(FORECAST(D2,OFFSET(B4,MATCH(D2,B5:B8),MATCH(D1,C4:K4),2),OFFSET(B4,MATCH(D2,B5:B8),,2))&REPT(" ",99)&FORECAST(D2,OFFSET(B4,MATCH(D2,B5:B8),MATCH(D1,C4:K4)+1,2),OFFSET(B4,MATCH(D2,B5:B8),,2)),{1,99},99)),),OFFSET(B4,,MATCH(D1,C4:K4),,2))

There is only one more element I need to add and that would be the ability to forcast exponentally below and above the listed PSI by the closest available delta as is done in the M5:M13 table of my worksheet.

MarvinP,
Sorry this was a duplicate posting. I will edit the OP to attach the example worksheet.

Thanks All