Matching a column of cells to another column in Excel Ok, I need to compare "A" column to "B" column. I have a billing agency that generates  a number i.e. 00125785231... This number could be in column A at A80 and then in column b at B801... I need something to show in column C801 that it has a match in A column.
Vlookup will be the answer here.

Eg, given a table:

   A,B
   1,5
   2,6
   3,7
   4,8
   5,9


In C1 put in, and drag it down:

=IF(ISNA(VLOOKUP(B1,$A$1:$A$6,1,FALSE)),"NOT IN A","IN A")


Col A has to be sorted.

Output:

A,B,C
1,5,In A
2,6,In A
3,7,Not in A
4,8,Not in A
5,9,Not in A
6, 10,Not in A


To deconstruct this:

   (VLOOKUP(B1,$A$1:$A$6,1,FALSE)


VLOOKUP takes your input (B1), and finds that value in the search table (A1:A6). The $ stops this changing as you drag down. It pulls back column 1 of the search table, in this case that's Column A. False tells it not to fuzzy match

=IF(ISNA(VLOOKUP(B1,$A$1:$A$6,1,FALSE)),"NOT IN A","IN A")


Wrapping this in an ISNA tells the IF to print out "NOT IN A" for successful ISNA - ie. where you get NA as a value from the vlookup, meaning it can't find a value. Otherwise, it prints IN A, which is where it can find a value.

Might be more than you care to learn about VLOOKUP (HLOOKUP is the horizontal equivilent), but with a larger search table, you can then pick different values that match your input. This approximates a SQL join.

So, given a table of client data:

Name, ID, Phone
Alex, 1, 123-456-7890
Bob, 2, 111-111-1111
Alice, 3, 222-222-2222
Teresa, 4, 333-333-3333


You can then create another table with a second set of data, and use a column in that to pull in data from this table.

eg.

OrderNum, ClientID, Client Name
1, 3, VLOOKUP - Alice
2, 4, VLOOKUP - Teresa
3, 2, VLOOKUP - Bob


This generally useful when given a report to be enriched.