CODEWITHSUNDEEP
formulalibreoffice-calcopenoffice-calc
Akira Takeshi
Akira Takeshi

Reputation: 124

LibreOffice Calc: Find a cell by value

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”.

Upvotes: 0

Views: 1846

Answers (1)

JohnSUN
JohnSUN

Reputation: 2539

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

Upvotes: 1

Related Questions