Need to look up value in another Sheet based on criteria in first sheet.

[Stacey]

Here is what I'm trying to do:

In sheet 1, I have a a column that lists values:

Example:

Dog
Cat
Cow
Pig

In Sheet 2, each month I paste a table that is computer generated. It also
lists these same values as well as a number in the column next to the
subject:

Example:

Dog 2
Unicorn 20
Cat 5
Cow 7
Horse 9
Pig 1

You'll notice that based on this example, the two lists don't always match
(alphabetical orderwise), and the data in the second sheet will expand and
contract from month to month (i.e. animal names can change from month to
month in sheet 2, but stay stable in sheet 1).

I need a function that I can put in Sheet 1 Column B, that will look up the
numberical value in Sheet 2 that corresponds to the subject (animal) listed
in Sheet 1.

Example, I want sheet one to automatically look up the number and insert it
in the column next to the animal name:

Dog 2
Cat 5
Cow 7
Pig 1

Is there a function I can put in the second column in sheet 1 that will do
this?

THanks!

--Stacey

 

[Max]

One way ..

Assuming the source table is pasted in Sheet2,
cols A & B, from row1 down

In Sheet1,

With the lookup values in A1 down

Put in B1:
=IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2,0))
Copy down

Alternatively, a slightly longer
variation which could be placed in B1:
=IF(A1="","",
IF(ISNA(MATCH(A1,Sheet2!A:A,0)),"",
VLOOKUP(A1,Sheet2!A:B,2,0)))

(Above will return blank for any unmatched case,
instead of ugly #N/As)