OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

lookup formula

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
qwertyjjj
Power User
Power User


Joined: 05 Sep 2010
Posts: 66

PostPosted: Sun Jan 30, 2011 4:15 am    Post subject: lookup formula Reply with quote

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???
Back to top
View user's profile Send private message
keme
Moderator
Moderator


Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

PostPosted: Sun Jan 30, 2011 4:29 am    Post subject: Reply with quote

You can use that column number in the INDEX() function.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group