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?
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?
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
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)
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.
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 – 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”.