how to extract information from one table to another one

[Omics]

Question:

For the example below, if one would extract the “Food†information from
Table 2 and put it to the corresponding “Food†column in Table 1, what’s the
best way to do this? Is there a way without using macro?

The expecting result for the example below
Area Food
AA apple
BB orange
CC cherry
DD banana

My real data has more than 10,000 rows. Thus I would very much appreciate
your help.

Omics


Example:
Table 1

Area Food
AA
BB
CC
DD



Table 2
Area Food Store

AA apple x
AA apple y
AA apple z
DD banana 3
DD banana m
BB orange 1
CC cherry 10
FF potato v
GG peanut s
GG peanut a
HH fish 6
BB orange j
EE tomato l
EE tomato 8

 

[Janusz Pawlinka]

Question:

For the example below, if one would extract the "Food" information from
Table 2 and put it to the corresponding "Food" column in Table 1, what's
the
best way to do this? Is there a way without using macro?

The expecting result for the example below
Area Food
AA apple
BB orange
CC cherry
DD banana

My real data has more than 10,000 rows. Thus I would very much appreciate
your help.

I assume that Table1 and Table2 are separate worksheets in the same
workbook,
and Area and Food are columns A and B in these worksheets.

In Table1 column Food cell B1 try this (when A1 ie AA)
=VLOOKUP(A1, Table1!A:B;2;FALSE)
and then copy that formula down below

This works fime when there is one Food for one Area. When there is more than
one
Food for one Area (ie apple and pear for AA) - formula returns that value
which is first
founded (when first apple is in row 24 and first pear in column 36 - formula
returns apple).

 

[Omics]

Thanks. Tried it but it said formula error.

Omics

 

[David Biddulph]

You'll need to use either commas or semi-colons, depending on what you use
as your list separator (specfied in Windows Regional Options), but not a
mixture of the two. :-(

 

[Janusz Pawlinka]

You'll need to use either commas or semi-colons, depending on what you use
as your list separator (specfied in Windows Regional Options), but not a
mixture of the two. :-(

Of course. Thanks for help and explaining.
I use polish version of Excel (with semi-colons as separators), but I must
'translate' formulas from polish to 'normal' (I mean english/US) version
to show its here.
In polish version of Excel formula from my last e-mail looks like this:

=WYSZUKAJ.PIONOWO(A1; Table1!A:B; 2; FA£SZ)

It is a bit confusing (stupid) for me that I cannot use english names of
formulas in cells in worksheets in polish version of Excel. The rather that
I have to use english names of formulas when I write macros in Excel PL.

Thanks again

 

[Omics]

Thanks so much, Janusz and David! It finally worked out by using following
formula:
=VLOOKUP('1'!A1, '2'!A:B,2,FALSE)

Omics