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

t83494.ods (13.1 KB)

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.

Crossposted and also with a working answer: Come individuare la cella in cui si trova un valore?

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)`

`{=TEXTJOIN(";";1;IF(\$A\$1:\$E\$3=0.005;ADDRESS(ROW(\$A\$1:\$E\$3);COLUMN(\$A\$1:\$E\$3);4)))}`