Find cell location in a large list
 

I have two lists, one with 130 cells and the other with 18,000 cells. The
numbers in the shorter list are also listed in the longer list, but I need to
know the cell location number. Is there any way to do this without going to
each individual cell and using the Ctrl+F function.

Find cell location in a large list
 

Hi,

Long list in column A, short list in column B, put this in C1 and drag down
as long as column B

=ADDRESS(MATCH(B1,$A$1:$A$65000,0),1)

Mike


"TVC" wrote:

I have two lists, one with 130 cells and the other with 18,000 cells. The
numbers in the shorter list are also listed in the longer list, but I need to
know the cell location number. Is there any way to do this without going to
each individual cell and using the Ctrl+F function.

Find cell location in a large list
 

Mike,

Thanks. I get #N/A when I input the formula. Does the formula depend on
the format, number,text, etc.?

"Mike H" wrote:

Hi,

Long list in column A, short list in column B, put this in C1 and drag down
as long as column B

=ADDRESS(MATCH(B1,$A$1:$A$65000,0),1)

Mike


"TVC" wrote:

I have two lists, one with 130 cells and the other with 18,000 cells. The
numbers in the shorter list are also listed in the longer list, but I need to
know the cell location number. Is there any way to do this without going to
each individual cell and using the Ctrl+F function.

Find cell location in a large list
 

That means it isn't finding a match. If your sure there is one then to test
the data manually find a match and this would be in (say) A55 and B1

put this formula in a cell

=A55=B1

If they are really the same then this will evaluate as True, if you get
false then your data may contain rogue spaces so try this

=len(A55)=Len(b1)
if this evaluates as false then it's spaces.

Mike
"TVC" wrote:

Mike,

Thanks. I get #N/A when I input the formula. Does the formula depend on
the format, number,text, etc.?

"Mike H" wrote:

Hi,

Long list in column A, short list in column B, put this in C1 and drag down
as long as column B

=ADDRESS(MATCH(B1,$A$1:$A$65000,0),1)

Mike


"TVC" wrote:

I have two lists, one with 130 cells and the other with 18,000 cells. The
numbers in the shorter list are also listed in the longer list, but I need to
know the cell location number. Is there any way to do this without going to
each individual cell and using the Ctrl+F function.

Find cell location in a large list
 

I was able to get the formula to work. I noticed the address result is not
correct. The address result will tell me the number is in cell a12, but
number I am looking for is really in a13. Is there something I can do to get
the exact cell number? If the number I am looking for is in the largre
column more than one time, is there a way to list all the cell numbers using
the formual you gave me?

Thanks again for your help.

"Mike H" wrote:

That means it isn't finding a match. If your sure there is one then to test
the data manually find a match and this would be in (say) A55 and B1

put this formula in a cell

=A55=B1

If they are really the same then this will evaluate as True, if you get
false then your data may contain rogue spaces so try this

=len(A55)=Len(b1)
if this evaluates as false then it's spaces.

Mike
"TVC" wrote:

Mike,

Thanks. I get #N/A when I input the formula. Does the formula depend on
the format, number,text, etc.?

"Mike H" wrote:

Hi,

Long list in column A, short list in column B, put this in C1 and drag down
as long as column B

=ADDRESS(MATCH(B1,$A$1:$A$65000,0),1)

Mike


"TVC" wrote:

I have two lists, one with 130 cells and the other with 18,000 cells. The
numbers in the shorter list are also listed in the longer list, but I need to
know the cell location number. Is there any way to do this without going to
each individual cell and using the Ctrl+F function.