... and to thus sidestep the separate "robustness" check. ...
Hi Jobowo
2 remarks.
A - The word
robust
I'm sorry I was not clear. I'm under the impression that you think that I wrote that my formula is
robust because I use a countif to check if there is a solution.
I'll try to clarify.
I did not use the word
robust with its usual meaning as an English word, rather as a word from excel's technical jargon related to formulas, that we've been using in this board for some years (I believe it was introduced by Aladin).
We say a formula is
robust when it survives changes in the location of the data, either as a result of a move or as a result of insertion/deletion of rows/columns.
To make it clear, consider these 2 cases
1 - after the formula is inserted
Test the following cases:
- insert a row above the table
- insert a column to the left of the table
- select the whole table and move it to another location
You can check that my formula is indifferent to any of these actions
2 - before the formula is inserted
Suppose a user sees this solution and wants to deploy it in another worksheet but where the data instead of being located in A1:H9 is in somewhere else. If you just replace in the formula the old locations with the new ones the formula will work without any changes.
This is what I meant with my formula being robust.
B - your solution
For what it's worth, my comment about the solutions.
Your solution does not address the general problem of this thread as stated in the title but that's also not what you wanted to post. You wanted to post a solution to the problem stated in by Mhenk and your solution works perfectly according to the specific data in this problem.
My formula and xld's address the more general problem of a reverse lookup in a table: given the value of an element in a table get it's row/column headers.
An experienced user will understand your formula and will use it and adapt it with no problem. I think a less experienced user will probably think the other formulas are easier to adapt to other cases, especially if the headers must be read in the header row/column.
Mhenk has several solutions that solve this problem. She/he will choose the one that works best for the specific case.
Cheers.