Ask Your Question
3

What is the best way to get the value from a rectangular table? [closed]

asked 2012-06-12 21:43:35 +0100

JohnSUN gravatar image

updated 2012-06-12 21:56:10 +0100

In a rectangular table has a unique headings of column and rows.

=OFFSET(<start_of_table>;N(MATCH(<row_header>;<first_column_of_table>;0));N(MATCH(<column_header>;<first_row_of_table>;0)))

Is there any way easier?

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-10-26 21:24:02.982048

2 Answers

Sort by » oldest newest most voted
1

answered 2013-03-13 09:52:50 +0100

qubit gravatar image

@JohnSUN,

Sorry we couldn't provide an good solution for you. You provide so many answers on this site that when you pose a question, we know it's going to be tough to answer! :-)

It's possible that you might just need to file an enhancement bug about this one and ask for some beefier tools to tackle the job.

Danke.

edit flag offensive delete link more

Comments

Thank you for the flattering review, my friend. I do not think that such a trifle as a lookup in table can be an occasion for the registration bug. Also, I do not believe that my bug will attract the attention of developers. For example, bug#50846 remained uncorrected in 4.0.1.2.

JohnSUN gravatar imageJohnSUN ( 2013-03-13 10:15:21 +0100 )edit

@JohnSUN -- true, the bug might not get noticed too quickly, but perhaps it can attract the attention of some other people who would benefit from the same enhancement.

I guess the answer for now is "No, we don't know of an easier way to get the value from a rectangular table".

qubit gravatar imagequbit ( 2013-03-13 10:23:12 +0100 )edit
4

answered 2012-06-28 13:34:47 +0100

erAck gravatar image

Sounds like you want to define column and row labels and ranges under menu Insert->Names->Labels and then in a formula simply use

=columnlabel rowlabel

If labels contain blanks enclose them in single quotes

='column label' 'row label'

You can also use the technically equal ! intersection operator

=columnlabel!rowlabel

edit flag offensive delete link more

Comments

2

Thank you, this is a very elegant solution! But it works only for clearly specified names (if the names specified directly in the cell). I'm trying to find a simple formula for the case where the name of the row and column name selected in the individual cells with the drop-down(Data-Validity)

JohnSUN gravatar imageJohnSUN ( 2012-06-28 13:53:48 +0100 )edit

Question Tools

Stats

Asked: 2012-06-12 21:43:35 +0100

Seen: 219 times

Last updated: Mar 13 '13