match up column A with column B


I have a column of numbers in A, and a column of numbers in B.  There
are 180 numbers in A (A1:A180) and 130 numbers in B (B1:B130).  I need
Excel to find which numbers match up between columns, e.g look down
column A and find all the matching numbers in column B, then sort the
columns so the matched numbers are next to each other (so I can see
which numbers don't match between columns). (I have sorted the numbers
from lowest to highest down the column, so they are in numerical

I have no idea how to do this. 

Thanks for any assistance you can provide.  :)

Micronaut's Profile:
View this thread:

5/31/2005 3:16:36 PM
excel 39879 articles. 2 followers. Follow

5 Replies

Similar Articles

[PageSpeed] 46

Here's one way:

Insert a blank column B (current B becomes C)

In B1, enter this formula:


and copy this formula down to B180

Column B will be blank if the value in A is not found.  If the value in
A is found, the matching entry will be returned.

Will this work for you?


swatsp0p's Profile:
View this thread:

5/31/2005 3:30:04 PM
swatsp0p Wrote: 
> Here's one way:
> Insert a blank column B (current B becomes C)
> In B1, enter this formula:
> =IF(ISERROR(VLOOKUP(A1,$C$1:$C$130,1,0)),"",VLOOKUP(A1,$C$1:$C$130,1,0))
> and copy this formula down to B180
> Column B will be blank if the value in A is not found.  If the value in
> A is found, the matching entry will be returned.
> Will this work for you?


Thanks for the reply, that's worked well.  Is there any way to
highlight in the now column C, which numbers didn't match up?

Micronaut's Profile:
View this thread:

5/31/2005 3:43:06 PM
In C1, use Format>Conditional Formatting as such:

Choose Formula Is: =ISERROR(MATCH(C1,$B$1:$B$180,0))  and set
Format>Patterns to your desired fill color (I like Rose :) ) to
highlight cells that don't match in Col. B

Then copy this cell (C1) and Paste Special>Formats in range C2:C130

Does this work for you?


swatsp0p's Profile:
View this thread:

5/31/2005 4:01:17 PM

Yes, that also worked well.  I tried using the original formula as an
additional conditional format to highlight the matches displayed in
column B, but it's not highleted them all.  There weren't many "hits"
from column B so I sorted them manually from the list generated from
the original formula.

Thanks for your help with the problem, was much appreciated.  Sorted my
problem, literally!  :)

Micronaut's Profile:
View this thread:

5/31/2005 4:39:06 PM
I'm glad this worked for you.  Thanks for the feedback, it is always



swatsp0p's Profile:
View this thread:

5/31/2005 5:50:07 PM

Similar Artilces:

Matching function or no match
I am using this formula to compare 2 list to see which numbers are not included in both column A and Col C. The formula I am using below pulls out the ones that i do have a match . Is there another function that I could use that would only pull out the ones that there is not a match? =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),"",A1) Thanks Uh =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),A1,"") -- HTH Bob "Donna" <> wrote in message > > I am using this for...

Client E-Mail and Web E-Mail not matching
I've got a few complaints from about 4 users where e-mail they see in their Outlook Client is not showing up in their Outlook Web Client when they check their e-mail from a remote location. We're using Exchange 2003 with SP2 installed and all the users have Office Outlook 2003 with SP1 Installed also. This seems to only have been a problem since we upgraded to Exchange SP2 two weeks ago. (Although I can't confirm it, but I wasn't told of any problems before th upgrade.) We had to upgrade to SP2 because of the 16 Gig limit so rollback is not an option. Best as I can t...

Matching cells #3
Here's my spreadsheet: A B C 1 1 1 2 3 3 3 4 6 4 5 10 5 8 11 6 9 7 10 8 9 10 11 12 13 How do I get the matching numbers on the same rows, like: A B C 1 1 1 2 3 3 3 4 4 5 5 6 6 7 8 8 9 9 10 10 10 11 11 12 13 13 I would just re-create the columns. 1. Copy column A into D. 2. In E1 put: =IF(COUNTIF(B:B,$A1),$A1,"") 3. Copy it over to F1 and then down as far as needed. 4. Select columns D thru F, copy them, and go to Edit > Paste Special > Value. Press OK. 5. Delete column A thru C. HTH Jason Atlanta, GA &g...

column charts on two axis
I am trying to show a column chart on 2 axis. I have chosen the combination line and column chart to get the 2 axis but when I change the line to a second column it puts the columns over the top of the first axis columns. How do I get them to be side by side? Have a look at: best wishes -- Bernard V Liengme remove caps from email "Jo" <> wrote in message >I am trying to show a column chart on 2 axi...

Lookup Formula: Return 1st match, then 2nd match, then 3rd match
I have a spreadsheet that looks like the following. Tab 1. Col A=Name. Col B = Task. A B Bob Reconcile Cash Tim Do Sales Report Bob Create presentation Tim Prepare financial statements Bob Hire staff person Now on tab two, I want to create another list that pulls all the tasks together by person. For example, Tab 2 would look like this: Bob Reconcile Cash Bob Create presentation Bob Hire staff person Tim Do Sales Report Tim Prepare financial statement. I realize I can just sor...

Index Match Functions
Has anyone ever combined the Index and Match functions to do lookups? ...

Not matching transactions
I have it set to not automatically do anything. I've tried resetting the account quite a few times with the help of technicians, but it's too long and involved, and I'm tired of rebalancing the account every time. I have 3 accounts, 1 works without passport and does fine. One of the others I can manually download from the web site, and have no problems doing it like so and decided this is the best way, prior tries it wouldn't work with passport. But the other still insists they are new transactions, and I can't download from their site directly. They only allow...

Need column that will post aging date
I have spreadsheet with invoice dates on it. I need another column that will show aging date against specific date that will be input in one of the cells. Tom, Use =CellWithDate - CellWithSpecificDate like =B3-$C$1 Format for 0 decimals, and that will report days. Otherwise, you could use the DateDif function. See HTH, Bernie MS Excel MVP "tom" <> wrote in message news:lwTdi.5139$ >I have spreadsheet with invoice dates on it. I need > another column t...

data in row didnt match import type 12-30-05
while importing almost 800 accounts , i got the following error for majority of accounts 'Data in row did not match the import type' what is the solution? How can i match it? whats is the way of comparision? It looks like a data problem. You need to check if all dates are correct (and filled), if numbers are numbers (not letters), etc... Regards, -- Erik van Hoof CWR Mobility Check our weblog at: "Aam" <> wrote in message > while impo...

Change rows to columns for similar records
Hi - am trying to change rows to columns for similar records. How do I do that? e.g. Staff A Cellular No. XXXXX Staff A Office No. XXXX Staff B Cellular No. XXXXX Staff B Office No. XXXXX Wld like for it be in the following row Staff Cellular No Office No A XXXXX XXXXX B XXXXX XXXXXX Do advise. Thanks. one way would be Would be to set up your "heading" as such Cell Phone Staff A 1 2 Staff B 3 4 Then assuming that Staff A is in B8 Staff B is in B9 Cell is in C7 Phone is in D7 In cell c8 enter ...

formula for a column #2
I'm working on a daily worksheet where all the values for a column will be the same ex. L2/C2, L3/C3, L4/C4 How do I set it up? Assuming the column you want to compute the values is in say, col M Put in M2: =IF(C2=0,"",L2/C2) and press ENTER Point the cursor to the bottom right corner of M2 (cursor will turn into a "black cross") Drag to fill as far down as required .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik -- "msdobe" <> wrote in message news:529B3703-9D9A-4560-80D...

Manually matching transactions
Hi, I'm new to Money 2007 so maybe this is an easy question but I don't find an answer after searching help and recent posts. I have setup a checking account and a bill for Tuition. My transactions are automatically downloaded to my checking account. One of these transactions was my Tuition payment, but the bill still shows as being past due. Can't I simply mark that transaction as being a Tuition payment somehow? I'm sure I could record payment from the bill with the same info as that transaction has then delete the downloaded transaction that wasn't associated ...

Column heading differences
The menu column headings on my spreadsheets today are numbers instead of letters! I want the letters back! I like the differentiation between rows (numbers) and columns (letters). Help?? I received and opened a virus-free spreadsheet from a co-worker. Her spreadsheet had numbers for the columns -- could that have made my default switch? How do I switch it back? <Tools> <Options> <General> tab, And *UNCHECK* "R1C1 Reference Style". -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benef...

How does MATCH handle a Non-Match?
I would like a steer on how the MATCH Worksheetfunction behaves if it doesn't find an exact match of values in the array it is searching. I am looking through a series of Dates and instead of picking the nearest one - which I need and seems most obvious - it seems to pick the previous one. I.E. - Searching for 14 Oct 2005 In a list which contains (among others) 15 Sep 2005 16 Oct 2005 And it picks the 15 Sep row! PS. Cell values are actually 14/10/2005 but formatted as above. In Excel's Help for the MATCH function, it describes how the values are returned. Chris wrote: > I w...

sort 2 column in the same time "" as dictionary ""
Hello all I need to make sort of pages to be like a dictionary is that possible I put a test file on the next link I need to sort the 2 yellow column ,to be alphapetic page by page appreciate for help me Thanks Mahmoud ...

Reversed column labels
For some reason, when I open any new spreadsheets, the column labels are reversed, with column A on the right side of the screen. This is making working with excel difficult. Is there some option I enabled that can be disabled so that Excel gets back to normal? Thanks Andrew Remsen Hi Drew tools / options / international - check the settings under default direction note, you won't see the affect of this until you close & reopen your workbook. Regards julieD "Drew Remsen" <> wrote in message news:MeSpd.73932$

how to put(repeat 7000times) the word "available" in a column
I have 7000items in excel worksheet and I want to write the word "available" in the end of each line, please help me Hi select your 7000 lines by typing the range in the name box (little box to left of formula bar above the letter A) e.g. h1:H7000 and press enter this will select all the cells for you now type (don't click anywhere just type) available and press control & enter this will put it into each selected cell. -- Cheers JulieD check out ....well i'm working on it anyway "eksel" <

Column help
I have a seating chart I am trying to design for our graduation ceremony. Right now I have the report set up using columns. I have a rectange box with the graduates first, middle and last name in the box. I need 16 columns across the page for the seats which I have working. My problems are that I need a bold line going down the middle to divide the 8th and 9th rows. ( Students come in from 2 lines and meet in the middle of each row.) My next problem is that I need to have another column to the left of the 1st column and one to the right of the 16th column that will count the row num...

Index, Match, Min and Max question
I'm trying to retrieve a date (in column A) that corresponds with a Min and Max amount (in columns B:D). Basically I need to know when my amounts hit their Highs and Lows. Can someone help me with this please? Thanks, Maria Dates in column A; values in B Then =INDEX(A1:A9,MATCH(MAX(B1:B9),B1:B9,0)) Returns the date corresponding the max value in B Is this what was needed? best wishes -- Bernard V Liengme Microsoft Excel MVP remove caps from email <> wrote in message >...

Convert data from rows to columns
I have a range of information on a spreadsheet as follows: A B 1 Name: Tom Smith Job Title: Sales Organisation: Made up Telephone: 1111 111 111 Email: Subject: Not much I have another 300+ entries of data (of 8 rows exactly as above) totalling 2629 rows. the example above is how it appears on my spreadsheet i.e. headings and names in the same cell (Name: Tom Smith) and sometimes seperate cells (Email: / I need to create 6 columns for Name/Job title/Organisation/Telephone/Email/Subject and then move the data into the relevant columns....

How to create a column styled blog in Word
I want to use word to create content for a blog that looks like a magazine with 2 columns - how would I create a column blog ...

Putting formula into column from macro?
How do I load a formula into a column from a macro? I tried: ActiveSheet.Cells(6, 6) = "=SUM(H:H)" But it doesn't work..... Also, is there an object that returns the number of rows in a sheet? So I can loop until I reach that number? Suggestion...Please! Samuel, ActiveSheet.Cells(6, 6).Formula = "=SUM(H:H)" Rows.Count will return the number of rows in the sheet, but it is likely that you really mean the number of used rows. Sub Test() Dim i As Long For i = 1 To Cells(Rows.Count, 4).End(xlUp).Row Cells(i, 3).Value = "Filled in by the macro" Next i End Su...

version matching
Do both people have to have the same version of Outlook running in order for Tasks and Appointments to sync up properly when emailed between clients? I'm seeing some irregularities between someone running Outlook in Office 2000 and mine which is part of Office 2003. Appointments with reminders sent from Office 2002 or later will show up as text in Outlook 2000 unless a hotfix is applied to the Outlook 2000 installation. Besides this issue, I am not aware of any others except for ..pst file type incompatibilities, easily solved on the 2003 side. -- Milly Staples [MVP - Outlook] ...

Matching cells by content then cell fill with color
Thanks to JEM, I am using this routine to color three consecutive cell a specific color, in this case red: Public Sub ThreeCellsRed() ActiveCell.Resize(1,3).Interior.ColorIndex = 3 End Sub What I need now is a way for the routine to continue to find all th similar cells, let's say for sake of disc they are people's names, s when I execute the above on my name, mrh, I want it to continue in th worksheet and find all exact matches and color those same cells red. Another thought, say my name (MRH) is in "A1" and it is also in "D1". But in "D1" I use "...

Sort as "reference column"
Column A has a random sorted list of text values, column D also consists of same values but not in the way sorted as column A. What I want is to sort Column D just like Column A but with the adjacent columns to the right (Column E,F also) should sort together with column D. -- kingjeremy ------------------------------------------------------------------------ kingjeremy's Profile: View this thread: Hi One way would be to use helper columns. Start by adding a ...