qwertyjjj
Power User
Joined: 05 Sep 2010
Posts: 64
|
 Posted: Sun Jan 30, 2011 4:15 am Post subject: lookup formula |
 |
|
I have a dataset that looks like this, the top row is a header row and there are 3 rows in this data.
| Code: |
Hand T9 A9 L9 T8 A8 L8 T7 A7 L7 T6 A6 L6 T5 A5 L5 T4 A4 L4 T3 A3 L3 T2 A2 L2 T1 A1 L1
A8s 2.2 4.3 9.8 2.6 5.4 10 3.2 6.9 10 4 8.9 10 5.1 10 10 6.8 10 10 9.6 10 10 10 10 10 10 10 10
A8o 2.1 2.1 2 2.7 3.1 2.5 3.4 3.6 3.1 4.5 4.8 4 6 6.6 5.3 8.3 9.5 7.5 10 10 10 10 10 10 10 10
|
I am going to type into a cell on the right and I want to perform a lookup.
So,
Table A
Hand A8s
Number 5
Result ???
So from this search box, calc should lookup the top header and find A, concatenate this with the number 5 to find A5. Then it should look on the left and find A8s and it should return that cell value, which in this case is the value 10.
Any ideas how I can do that? I tried using lookup but I am not sure how I should get it to return another value dependent on another lookup?
I tried using MATCH to get the column but it returns the column number instead of the column letter so I can't use it.
I can get the colummn from this: =MATCH(AF7;A1:AB1;0)
and the row from this: =MATCH(AE6;A1:A170;0)
but I don't know how to get the cell from the column number and row number??? |
|
