MATCH OR VLOOKUP

[Steve]

Hi, I cant figure this one out.

In worksheet 1 I have a column of places names and another column
which has the country. In another worksheet I have the same
information in column A and B and in Column C I have the word Hot or
Cold

I would like to create a new column in the main worksheet which looks
at each record in Columns A and B (for example) then looks at the
Columns A and B in worksheet 2 and if they all match returns the value
in column C of worksheet 2


Worksheet 1

A B C

London England Hot
Edinburgh Scotland
York England
Perth Scotland
Glasgow Scotland
Newcastle England


Workseet 2


A B Climate

London England Hot
Edinburgh Scotland Cold
York England Hot
Perth Scotland Cold
Glasgow Scotland Cold
Newcastle England Hot


So I would like Column C of workshet 1 to be whatever is in the
climate column of worksheet two when Columns A and B are the same in
both worksheets


I hope this is clear and as always any help appreciared


Thanks



Steve

 

[Claus Busch]

Hi Steve,

Am Sat, 14 Dec 2013 13:13:17 +0000 schrieb Steve:

Worksheet 1

A B C

London England Hot
Edinburgh Scotland
York England
Perth Scotland
Glasgow Scotland
Newcastle England

Workseet 2

A B Climate

London England Hot
Edinburgh Scotland Cold
York England Hot
Perth Scotland Cold
Glasgow Scotland Cold
Newcastle England Hot

Sheet1 C1 try:
=INDEX(Sheet2!C:C,MATCH(A1&B1,Sheet2!$A$1:$A$10&Sheet2!$B$1:$B$10,0))
and array-enter the formulka with CTRL+Shift+Enter and copy down


Regards
Claus B.

 

[Claus Busch]

Hi Steve,

Am Sat, 14 Dec 2013 14:25:07 +0100 schrieb Claus Busch:

=INDEX(Sheet2!C:C,MATCH(A1&B1,Sheet2!$A$1:$A$10&Sheet2!$B$1:$B$10,0))
and array-enter the formulka with CTRL+Shift+Enter and copy down

if you don't have same cities in different countries VLOOKUP works also:
=VLOOKUP(A1,Sheet2!$A$1:$C$10,3,0)


Regards
Claus B.

 

[Steve]

Hi Claus, thank you so much that worked perfectly.

Cheers


Steve