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.1 | 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”.
Thanks for your answer. It seems to me a system too cumbersome and dispersive. Have I to understand that there is no “true” way to find a value in a vector?
A vector is a single row or column. MATCH looks up a search value in a vector. Your range is 2-dimensional.
Ok. So have I to understand that there is no “true” way to find a value in a 2-dimensional range?
I’m not aware of any direct way.
I don’t know why you need the address. May be you want to highlight the search value?
t83494b.ods (13.1 KB)
(conditional formatting)
EDIT: Sorry, I uploaded the old file twice. t83494b us the right one.
Yes. Because the idea is not “compatible” with spreadsheet fundamental concept, where your rows represent “records”, and columns represent different “properties” (fields) of the records (sometimes people prefer exchange columns and rows, but that doesn’t change the principle). In your case, you seem to keep a “property” in different columns and rows (2D range). Indeed, spreadsheets are not databases, and are flexible to allow users any entry anywhere, but the functions are built around this. The best solution would be to re-arrange the data to have a single column of 15 rows. If needed, you could have auxiliary data in another column, helping you to know where a specific sample came from.
A “standallone formula” If you want to call it that.
=ADDRESS(MATCH(1,MMULT(A1:E3=G1,ROW(1:5))>0,0),MAX(MMULT(A1:E3=G1,ROW(1:5))),4)
Cell_ADDRESS.ods (16,9 KB)
Good idea, but needs to be clarified for the case where there can be equal values in the range.
For example, E3=55.
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)))}