On a calc sheet I have a vector with the following values:
| a | b | c | d | e | |
|---|---|---|---|---|---|
| 1 | 3.14 | 3.11 | 27 | 2.12 | 0.005 |
| 2 | 31 | 7.21 | 55 | 32.12 | 0.003 |
| 3 | 45 | 8.31 | 12 | 7.77 | 0.515 |
Is there a way to determine in which row and in which column a certain value is found?
For example: using the value “55” I need a formula that returns me “C2”, using the value “0.005” instead the formula should return me “E1”.
Perhaps this solution will help you:
compare all the cells in the original range with the value... If the value does not match, then use an empty string. Otherwise, use the ADDRESS() function to get the coordinates of the cell. Combine the results with TEXTJOIN() and don't forget that this is an array formula, complete the formula with Ctrl+Shift+Enter .
{=TEXTJOIN(";";1;IF($A$1:$E$3=0.005;ADDRESS(ROW($A$1:$E$3);COLUMN($A$1:$E$3);4)))}
Please, be careful. I hope that you randomly generated values like 3.14, 3.1, 0.515 just for example, but in fact your values are more accurate. The thing is, if you look up something like =1/1.9415 in your table, you won't get E3 as the result, since 0.515 in the table will not equal 0.515066 as a result of the calculation