join data rows from 2 worksheets

  • Thread starter Thread starter console1
  • Start date Start date
C

console1

Hello


I have Worksheet 1

columnA has data like
yellow
green
red
....


columnB has data like
chair
table
window
....

----------------------------------------

Worksheet 2 has

columnA
big
small
medium
....


columnB has data like
table
window
chair
....


I want to join the data from the two tables using matches from columns B

How do I do that?

thanks!
 
If it is possible to reverse the A & B Columns then:

=VLOOKUP("Chair",Sheet2!A1:B3,2,FALSE)& "
"&VLOOKUP("Chair",Sheet1!A1:B3,2,FALSE)& " Chair"
=VLOOKUP("Table",Sheet2!A1:B3,2,FALSE)&"
"&VLOOKUP("Table",Sheet1!A1:B3,2,FALSE)&" Table"
=VLOOKUP("Window",Sheet2!A1:B3,2,FALSE)&" "
&VLOOKUP("Window",Sheet1!A1:B3,2,FALSE)&" Window"

returns:
medium yellow Chair
big green Table
small red Window

respectively

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Unfortunate breaks in the formulas, hopefully this will be better:

=VLOOKUP("Chair",Sheet2!A1:B3,2,FALSE)
& " "&VLOOKUP("Chair",Sheet1!A1:B3,2,FALSE)
& " Chair"

=VLOOKUP("Table",Sheet2!A1:B3,2,FALSE)
&" "&VLOOKUP("Table",Sheet1!A1:B3,2,FALSE)
&" Table"

=VLOOKUP("Window",Sheet2!A1:B3,2,FALSE)
&" "&VLOOKUP("Window",Sheet1!A1:B3,2,FALSE)
&" Window

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Using Sheet1 as your main sheet, enter this formula in C1 of Sheet1 to
return the sizes from Sheet2:

=INDEX(Sheet2!$A$1:$A$3,MATCH(B1,Sheet2!$B$1:$B$3,0))
And copy down as needed.

You could reverse the formula, to enter it in C1 of Sheet2, to return the
colors from Sheet1:

=INDEX(Sheet1!$A$1:$A$3,MATCH(B1,Sheet1!$B$1:$B$3,0))
 
Back
Top