Locate Number in Table and Return Coordinates.

**alexie0830** · 06-22-2010, 06:24 PM

All,

I am trying to figure out the way how to accomplish the following:

I have a table 100 x 100 (cells)

I need to find a number (lets say "53") in this table, and return 2 values.

1 value for Row, and 1 value for column.

For example:

A, B, C, D - columns
1
2 53
3
4

I need to search for number "53" .. and the result should be Row = 2, Column = B.

Any insight will be greatly appreciated.

Thanks

Alex

**daddylonglegs** · 06-22-2010, 06:42 PM

Do you want that in 2 cells or all in one like B2 or $B$2?

Could 53 appear more than once, if so which address should it return?

**shg** · 06-22-2010, 06:45 PM

alexie, please take a few minutes to read the forum rules, and then amend your thread title accordingly.

Thanks.

**alexie0830** · 06-22-2010, 07:16 PM

The result should be in 2 cells..

Once cell Row number
Another cell Column number.

If more than one 53 exists in the table, list all coordinates.

Thanks

**shg** · 06-22-2010, 08:08 PM

If the value only appears once in the table, this returns the address where it appears:

=ADDRESS(SUMPRODUCT((tbl=53)*ROW(tbl)), SUMPRODUCT((tbl=53)*COLUMN(tbl)), 4)

... where tbl is the named range for the table, or just a reference to the table range.

**alexie0830** · 06-22-2010, 09:32 PM

Hi ... Its almost what i meant... but in this formula, the result comes back with 1 value. (H5) ...

What i am looking for is 2 values. H and 5, where H is a column and 5 is a row...

**alexie0830** · 06-22-2010, 09:37 PM

I have a Table with Horizontal labels from 1 thru 100 and Vertical labels from 1 thru 100.

InnerTable has all these random numbers (10,000 numbers total). What i am looking for is return 2 values... 1 value for the Horizontal Label (Columns) and another value for Vertical Label (rows) ...

So essentially, if i am searching for number "53" .. It should return 8 and 7 (where 8= Column I, and 7 equals row = 7).

**anubhav.pec** · 08-22-2013, 01:31 PM

Did this get solved? I have same problem :-/

**arlu1201** · 08-22-2013, 01:41 PM

Anubhav,

This is a 3 yr old question and not likely that the original poster will come back to you. Its better you post a new thread with your question. You will get faster replies.