Excel question

Dubsey

Hi all,

I've got an excel question please.

Basically I've got a table comprising of a header row 'A' and a colun 'B'. depending on the values for A & B gives the corresponding answer. See the attached table as an example. If I know that A = 1 and B = 2 can I enter a formula to tell Excel to look up the table to output the correct value depending on where the relevant row and column meet?

Many thanks for any help!

D.

rockal

Two way Table Lookup http://j-walk.com/ss/excel/usertips/tip020.htm

Dubsey

Hi Rockal,

Thanks for that - just what I was looking for.

Having said this, I've encountered another problem that has me stumped and I was hoping you can come to the rescue again.

So, the reason I need the look-up is because I have 2 variables that change ( "S" and "α") and i need to be able to retreive the corresponding value for the intersection between the various pairs of S and α values.

So, I've got my table with the S values across the top row and the α values down the left hand column. All fine and dandy. Then I enter the formula "=INDEX($M$1:$W$201, MATCH(AC7,$M$1:$M$201,0), MATCH(AB7,$M$1:$W$1,0))" and it works. I can even drag the formula down to the cells that follow and it gives me the correct answers which is what I want. Great!

BUT - when I copy the formula to another cell (the one i want the answers to appear in) and drag it down I get "#N/A" although I can't see anything wrong with the formula. All the cells that I want locked are locked by using the $ sign (at least I think I've used it correctly) with the exception of the two variables but it isn't working.

I've attached the said spreadsheet. Cells that are working fine are in green, the ones I'm having problems with are in red.

Any ideas?

Thanks again,

D.

rockal

OK Dubsey

Seems to be a problem with number of decimal places.

In Columns 'E' & 'F' try using =ROUND(D55/C55,1) and =ROUND((C55*D55)/$B$47^2,2)

Dubsey

Worked a treat - every day's a school day.

Thanks again,

D.