Search data from one column to find if it is present in another column.

**Rich@CES** · 01-18-2014, 08:39 AM

I am trying to search data from one column and see if it is in a second column along with the location in that column. I realize I can use the find function line by line but my Lookup_values are in a list of 1200 and the Lookup_array is a list of 22,000. Currently using =MATCH(Lookup_value,Lookup_array),1 and in a third column (I'm calling the Match Column) to show me the location of my lookup value. Can someone please show me a way to create a formula that I can paste in the third column (Match Column) that will allow me to not have to type the formula in each cell in my third column. Please see example below. =MATCH(Completed Units, Total Units:Total units)1

Completed Units Match Total Units
75007407 1 75007407
75015211 3 75015331
75015331 2 75015211

**hemesh** · 01-18-2014, 08:49 AM

Match find the row number of matched item

=match(value you want to match, list of item in which you want to perform your match,0)
assuming your data starts from A2

then
=MATCH(B2,$A$2:$A$100,0)
where b2 is the value you want to match
$A$2:$A$100 = list in which to perform match
0 is for exact match

**Rich@CES** · 01-18-2014, 09:09 AM

Hemesh, that formula is how I have been doing it. I want to be able to have it do it automatically without having to type the formula in for each cell in my match column

**RobertMika** · 01-18-2014, 03:35 PM

Originally Posted by Rich@CES

Hemesh, that formula is how I have been doing it. I want to be able to have it do it automatically without having to type the formula in for each cell in my match column

You do not need to retype.
Paste into first cell and then drag it down.(or copy of you prefer)