Hello,

I have 2 excel sheets with exactly the same format, in column A, I have the name of my customers and in Column B, the value of their sales. the other sheet contains exactly the same information but not in the same order. Now what I want to do is : If the name of my first customer (A1,sheet1) exists in the A column of sheet 2 then bring the sales value to cell C1 of the first sheet.

Sheet 1 ------------------ Sheet 2

A --------- B ---------------- A ------ B

X --------- 500 ---------------- Y ------- 540

Y -------- 1000 ---------------- Z ------- 100

Z -------- 200 ----------------- X ------- 800

And I need the result as this :

A -------- B -------- C

X -------- 500 -------- 800

Y -------- 1000 -------- 540

Z -------- 200 -------- 100

Ask a question
Report

# How to match two columns [Solved]

Paris - Latest answer on Aug 30, 2010 03:27AM

Read more

- How to match two columns
- Match data in two columns
- Compare values in two columns and return the value from third (Solved) » Forum - Excel
- Match two columns and copy specific cells » Forum - Excel
- Matching two column withing two different she » Forum - Office software
- Matching Two Columns of Data » Forum
- Match two columns in different sheets » Forum - Excel

Hello,

use a VLOOKUP formula ; something like =VLOOKUP(A2;Sheets2!A:B;2;FALSE)

if the customer does not exist in the second sheet, this will return #N/A

I.

use a VLOOKUP formula ; something like =VLOOKUP(A2;Sheets2!A:B;2;FALSE)

if the customer does not exist in the second sheet, this will return #N/A

I.

HI IVAN

i expect a similar kind of solution for my problem.

i have a list of 6 digit cheque numbers in ascending continuos serial order. for example, 223000,223001,223002.....etc. in one column 'A'.

then i have a list of 6 digit enchashed cheque numbers randomly in ascending oreder. i.e., 223002,237652, 238645,245674......etc like this in coumn 'B'

now i want to match these two columns and form a 3rd column with all un-encashed cheque nunbers.

akindly assume that all cheque numbers in column 'B' are defenitely available in column 'A'.

in terms of set theory, i want A-B ( here 'B' is a subset of set 'A').

kindly provide me with some solution for this

thank u

with regards

karthik

i expect a similar kind of solution for my problem.

i have a list of 6 digit cheque numbers in ascending continuos serial order. for example, 223000,223001,223002.....etc. in one column 'A'.

then i have a list of 6 digit enchashed cheque numbers randomly in ascending oreder. i.e., 223002,237652, 238645,245674......etc like this in coumn 'B'

now i want to match these two columns and form a 3rd column with all un-encashed cheque nunbers.

akindly assume that all cheque numbers in column 'B' are defenitely available in column 'A'.

in terms of set theory, i want A-B ( here 'B' is a subset of set 'A').

kindly provide me with some solution for this

thank u

with regards

karthik

hi xiao,

the above clomns are not correct.

see thie below column orders.

A B C

Issued Encashed Un-encashed cheq

230001 230002 230001

230002 230005

230003 230008 230003

230004 230009 230004

230005 230014

230006 230020 230006

230007 230024 230007

230008 230029

230009 230030

230010 230032 230010

230011 230033 230011

230012 230012

230013 230013

230014

230015 230015

230016 230016

230017 230017

230018 230018

230019 230019

230020

230021 230021

230022 230022

230023 230023

230024

230025 230025

230026 230026

230027 230027

230028 230028

230029

230030

230031 230031

230032

230033

230034 230034

the above clomns are not correct.

see thie below column orders.

A B C

Issued Encashed Un-encashed cheq

230001 230002 230001

230002 230005

230003 230008 230003

230004 230009 230004

230005 230014

230006 230020 230006

230007 230024 230007

230008 230029

230009 230030

230010 230032 230010

230011 230033 230011

230012 230012

230013 230013

230014

230015 230015

230016 230016

230017 230017

230018 230018

230019 230019

230020

230021 230021

230022 230022

230023 230023

230024

230025 230025

230026 230026

230027 230027

230028 230028

230029

230030

230031 230031

230032

230033

230034 230034

hi xiao,

sorry i tried to paste the columns from excel sheet but its alignment gets changed after posting.

hence i type directly here.

---------A ----- ---------B -------- -------C--------

Issued cheq Encashed cheq Un-encashed cheq

230001 230002 230001

230002 230004

230003 230006 230003

230004 230010

230005 230016 230005

230006 230020

230007 230007

230008 230008

230009 230009

230010

230011 230011

230012 230012

230013 230013

230014 230014

230015 230015

230016

230017 230017

230018 230018

230019 230019

230020

230021 230021

230022 230022

kindly help me

than u

with regards

karthik

sorry i tried to paste the columns from excel sheet but its alignment gets changed after posting.

hence i type directly here.

---------A ----- ---------B -------- -------C--------

Issued cheq Encashed cheq Un-encashed cheq

230001 230002 230001

230002 230004

230003 230006 230003

230004 230010

230005 230016 230005

230006 230020

230007 230007

230008 230008

230009 230009

230010

230011 230011

230012 230012

230013 230013

230014 230014

230015 230015

230016

230017 230017

230018 230018

230019 230019

230020

230021 230021

230022 230022

kindly help me

than u

with regards

karthik

**Benzolio**- Jan 29, 2009 11:31PM

Try putting the following in C1 and dragging it to fill down column C just as far as you have data in column A :

=IF(COUNTIF(B:B,A1)<1,A1,"")

Alternately, you could use soething like this array formula in column C:

{=IF(COUNTIF(B:B,A:A)<1,A:A,"")} [press ctrl-shift-enter]

Although it would be an inefficient solution as is, it should theoretically work with some tweaking. I tried it this way, but as an array function on the entire row C, it took a few minutes wasting time to compare all the empty cells.

=IF(COUNTIF(B:B,A1)<1,A1,"")

Alternately, you could use soething like this array formula in column C:

{=IF(COUNTIF(B:B,A:A)<1,A:A,"")} [press ctrl-shift-enter]

Although it would be an inefficient solution as is, it should theoretically work with some tweaking. I tried it this way, but as an array function on the entire row C, it took a few minutes wasting time to compare all the empty cells.

hi xiao,

thank u for ur advice . i tried ur formula but i could not get the result as i desired. i explain my problem in the following way.

Sheet-1

A B C

Issued Encashed Un-encashed cheq

230001 230002 230001

230002 230005

230003 230008 230003

230004 230009 230004

230005 230014

230006 230020 230006

230007 230024 230007

230008 230029

230009 230030

230010 230032 230010

230011 230033 230011

230012 230012

230013 230013

230014

230015 230015

230016 230016

230017 230017

230018 230018

230019 230019

230020

230021 230021

230022 230022

230023 230023

230024

230025 230025

230026 230026

230027 230027

230028 230028

230029

230030

230031 230031

230032

230033

230034 230034

i want a result in column 'C' as above. we can also put column B data in 1st column and column A data in 2nd column but the result must be column C as abobe.the no. of data in column'B'(as above) will be defenitely less than that in column'A'(as above).

in a nutshell, out of 1000 cheques issued 700 cheques have been encashed randomly. but how to find out the remaining 300 cheques from the original list.

all data are in the same sheet. kindly tel me about what 'sheet!a:b" represent in ur earlier formula.

kindly help me.

thank u

with regards

karthik

thank u for ur advice . i tried ur formula but i could not get the result as i desired. i explain my problem in the following way.

Sheet-1

A B C

Issued Encashed Un-encashed cheq

230001 230002 230001

230002 230005

230003 230008 230003

230004 230009 230004

230005 230014

230006 230020 230006

230007 230024 230007

230008 230029

230009 230030

230010 230032 230010

230011 230033 230011

230012 230012

230013 230013

230014

230015 230015

230016 230016

230017 230017

230018 230018

230019 230019

230020

230021 230021

230022 230022

230023 230023

230024

230025 230025

230026 230026

230027 230027

230028 230028

230029

230030

230031 230031

230032

230033

230034 230034

i want a result in column 'C' as above. we can also put column B data in 1st column and column A data in 2nd column but the result must be column C as abobe.the no. of data in column'B'(as above) will be defenitely less than that in column'A'(as above).

in a nutshell, out of 1000 cheques issued 700 cheques have been encashed randomly. but how to find out the remaining 300 cheques from the original list.

all data are in the same sheet. kindly tel me about what 'sheet!a:b" represent in ur earlier formula.

kindly help me.

thank u

with regards

karthik

## Not a member yet?

**sign-up**, it takes less than a minute and it's **free**!

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

hai- Jul 15, 2009 05:36AMtra- Jun 22, 2010 05:41AM