How to Find the location of a value in a multi row/column Excel spreadsheet table?

Question

How do I find either the cell address or preferably the row and column of the value 119, or any other number in the table below?

The table does not contain row or column titles, only the numbers as shown.

I am looking for a worksheet solution (formula) and not a VBA solution.

Answer 1

Below is a general purpose answer based on VBasic2008's answer.

I modified the formulas to utilize defined names so that the cell references do not have to be hard coded in the formulas. This way both the data table and row / column formulas can be relocated to anywhere on the spreadsheet. It works for both numerical AND text based data.

I also included the =ADDRESS() function to return the absolute reference of the look up value.

For illustration purposes, a step by step example for Data Set 1 is shown replacing the hard coded cell references with defined names.

The Data Set 2 section is the simplified version just using one defined for each the row and column look up value.

You can download an example spreadsheet here: Look_Up_a_Value_in_a_Table.xls

Thanks to all of you: Solar Mike, VBasic2008, and pnuts

Click on the image to enlarge.

Answer 2

Well, clunky and you can expand it, but it does work:

Row is separate to column but you could put them together in one cell, does depend on how you want to use the results, but you did not specify that so I have done this...

You could use a choose() function or a lookup table with vlookup() to change the column result to a letter...

Answer 3

An Array Formula

This is an array formula and it has to be entered with control shift enter i.e. hold control shift and then press enter.

=MAX(IF(A1:J34=119,ROW(A1:J34)-ROW(A1)+1))

Remarks:

The value is searched by column i.e. A1, A2, ... B1, B2 ... i.e. if you had another 119 in cell D1 the result would still be 2, and if you had a 119 in cell c1 then the result would be 1.

For a column version just replace ROW with COLUMN:

=MAX(IF(A1:J34=119,COLUMN(A1:J34)-COLUMN(A1)+1))

Answer 4

Please try:

=MOD((K1-50),34)+1&" | "&1+(INT((K1-50)/34))

where K1 is your selected value.

Returns R | C. (Data in A1:J34 is not required.)