How do I sort and merge data?

[sveno]

Lets say I have a table:

NAME            ADDRESS
apple           tree
pear            land
orange(fruit)  mountain

And another table/sheet:

NAME    CODE
pear    4556
apple   7776
orange  3321

And I want to bring the 3 different columns together in one sheet:

NAME    CODE    ADDRESS
apple   7776    tree
pear    4556    land
orange  3321    mountain

How would I do it? Can I also highlight mis-matched rows or better yet, use a wildcard so it will find “orange” from “orange(fruit)”? Thank you!

[sveno]

You can do it with INDEX and MATCH formulas.

The third column can be merged into the second example sheet with:

=INDEX(Sheet1.B$1:B$3;MATCH(A1&".*";Sheet1.A$1:A$3;0))

INDEX will pick and show the cell you want (from the ADDRESS row)

MATCH will look for the correct row where A1 (pear* in this case) is in the first sheet.

You need to have regular expressions enabled in Tools->Options for the wildcard “.*” to work!