I have a column of data and want to be able to find what cell a number is in

for example
2614
2713
2818

I want to search for 2713 and excel tell me it is in cell A2, but with a
large number of search items. Instead of using the find function over and
over.

I put some numbers in A8:A40
In C8 I entered one of these numbers
In D8 I used =ADDRESS(MATCH(C8,A8:A40,0)+ROW(A8)-1,COLUMN(A8),4)
This tells me the reference of the cell holding the same value as in C8
See Help for more info on ADDRESS
(Odd how MS always talks about cell 'references' but this function has the
name 'address' !)
If your data is in some other range change A8:A40 to reflect this, change A8
to the reference of the first cell holding the data

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Tabatha" <Tabatha @discussions.microsoft.com wrote in message
...
I have a column of data and want to be able to find what cell a number is
in

for example
2614
2713
2818

I want to search for 2713 and excel tell me it is in cell A2, but with a
large number of search items. Instead of using the find function over and
over.

Should this work if I have a formula in the column that i am searching for
the data in. I can get it to work on a smaller scale but in my big
spreadsheet I keep getting #N/A so I was wonding if formatting was part of my
problem. Thanks

"Bernard Liengme" wrote:

I put some numbers in A8:A40
In C8 I entered one of these numbers
In D8 I used =ADDRESS(MATCH(C8,A8:A40,0)+ROW(A8)-1,COLUMN(A8),4)
This tells me the reference of the cell holding the same value as in C8
See Help for more info on ADDRESS
(Odd how MS always talks about cell 'references' but this function has the
name 'address' !)
If your data is in some other range change A8:A40 to reflect this, change A8
to the reference of the first cell holding the data

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Tabatha" <Tabatha @discussions.microsoft.com wrote in message
...
I have a column of data and want to be able to find what cell a number is
in

for example
2614
2713
2818

I want to search for 2713 and excel tell me it is in cell A2, but with a
large number of search items. Instead of using the find function over and
over.

I have just changed my column of numbers to formulas and the formula I gave
you still works.
Want to send me a file (remove TRUENORTH.) ?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Tabatha" wrote in message
...
Should this work if I have a formula in the column that i am searching for
the data in. I can get it to work on a smaller scale but in my big
spreadsheet I keep getting #N/A so I was wonding if formatting was part of
my
problem. Thanks

"Bernard Liengme" wrote:

I put some numbers in A8:A40
In C8 I entered one of these numbers
In D8 I used =ADDRESS(MATCH(C8,A8:A40,0)+ROW(A8)-1,COLUMN(A8),4)
This tells me the reference of the cell holding the same value as in C8
See Help for more info on ADDRESS
(Odd how MS always talks about cell 'references' but this function has
the
name 'address' !)
If your data is in some other range change A8:A40 to reflect this, change
A8
to the reference of the first cell holding the data

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Tabatha" <Tabatha @discussions.microsoft.com wrote in message
...
I have a column of data and want to be able to find what cell a number
is
in

for example
2614
2713
2818

I want to search for 2713 and excel tell me it is in cell A2, but with
a
large number of search items. Instead of using the find function over
and
over.