How to match to Worksheets

Hi,

Here is my problem. I have two Sheets with the following data:

sheet 1 sheet2

country sales country sales
US 100 Spain
US 300 Spain
US 400 Spain
France 500 US
France 600 US
France 700 US
Germany 200 US
Germany 150 US
Germany 350 France
France
France
France
France
France
Germany
Germany
Germany

I want to match to each country in sheet 2 the sales in sheet1. if i use
Vlookup i'll always get the first result(i.e for US i'll get 100, for France
500...). if US appears more times in sheet 2 than in sheet1, I want the cell
in sheet2 to be empty.
please advise how could i do it?

thanks,
David Messika

On Wed, 17 Dec 2008 08:52:07 -0800, ??? ?????
wrote:

Hi,

Here is my problem. I have two Sheets with the following data:

sheet 1 sheet2

country sales country sales
US 100 Spain
US 300 Spain
US 400 Spain
France 500 US
France 600 US
France 700 US
Germany 200 US
Germany 150 US
Germany 350 France
France
France
France
France
France
Germany
Germany
Germany

I want to match to each country in sheet 2 the sales in sheet1. if i use
Vlookup i'll always get the first result(i.e for US i'll get 100, for France
500...). if US appears more times in sheet 2 than in sheet1, I want the cell
in sheet2 to be empty.
please advise how could i do it?

thanks,
David Messika



If your country and sales in Sheet1 are in columns A and B
respectively starting on row 2 (below one header row)
and your country and sales in Sheet2 are also in columns A and B
respectively starting on row 2 (below one header row)
you may try the following formula in cell B2 of Sheet2:

=IF(COUNTIF(A$1:A1,A2)=COUNTIF(Sheet1!A$1:A$100,A 2),"",INDEX(Sheet1!$B$1:$B$100,MATCH(A2,Sheet1!A$1 :A$100,0)+COUNTIF(A$1:A1,A2)))

Adjust the 100 in three places to cover the rows in Sheet1 and copy
the formula down as far as you have countries in column A of Sheet2

Note: For this formula to work I have assumed that the all sales for a
country are grouped together on Sheet1 as this was the case in your
example. If that is not the case, this formula will not work.

Hope this helps / Lars-Åke

From your example, US have multiple sales with different amount, which sales
amount do you want to show in Sheet2 under sales.


--
If this posting was helpful, please click on the Yes button

Thank You

cheers,









"דוד מסיקה" wrote:

Hi,

Here is my problem. I have two Sheets with the following data:

sheet 1 sheet2

country sales country sales
US 100 Spain
US 300 Spain
US 400 Spain
France 500 US
France 600 US
France 700 US
Germany 200 US
Germany 150 US
Germany 350 France
France
France
France
France
France
Germany
Germany
Germany

I want to match to each country in sheet 2 the sales in sheet1. if i use
Vlookup i'll always get the first result(i.e for US i'll get 100, for France
500...). if US appears more times in sheet 2 than in sheet1, I want the cell
in sheet2 to be empty.
please advise how could i do it?

thanks,
David Messika

Thanks for you help
--
בברכה,
דוד מסיקה


"Lars-Ã…ke Aspelin" wrote:

On Wed, 17 Dec 2008 08:52:07 -0800, ??? ?????
wrote:

Hi,

Here is my problem. I have two Sheets with the following data:

sheet 1 sheet2

country sales country sales
US 100 Spain
US 300 Spain
US 400 Spain
France 500 US
France 600 US
France 700 US
Germany 200 US
Germany 150 US
Germany 350 France
France
France
France
France
France
Germany
Germany
Germany

I want to match to each country in sheet 2 the sales in sheet1. if i use
Vlookup i'll always get the first result(i.e for US i'll get 100, for France
500...). if US appears more times in sheet 2 than in sheet1, I want the cell
in sheet2 to be empty.
please advise how could i do it?

thanks,
David Messika



If your country and sales in Sheet1 are in columns A and B
respectively starting on row 2 (below one header row)
and your country and sales in Sheet2 are also in columns A and B
respectively starting on row 2 (below one header row)
you may try the following formula in cell B2 of Sheet2:

=IF(COUNTIF(A$1:A1,A2)=COUNTIF(Sheet1!A$1:A$100,A 2),"",INDEX(Sheet1!$B$1:$B$100,MATCH(A2,Sheet1!A$1 :A$100,0)+COUNTIF(A$1:A1,A2)))

Adjust the 100 in three places to cover the rows in Sheet1 and copy
the formula down as far as you have countries in column A of Sheet2

Note: For this formula to work I have assumed that the all sales for a
country are grouped together on Sheet1 as this was the case in your
example. If that is not the case, this formula will not work.

Hope this helps / Lars-Ã…ke