Lookup/Find help

[Rita Palazzi]

Windows XP Professional
Office 2000

Hypothetical, but hopefully you'll get the gist of it:


I have two worksheets. On worksheet #1, I have two columns. First
column is a list of entire workgroup by name and 2nd column is the hours
worked. On the second worksheet I simply have an list of names that
is a subgroup of those on the first page. These indicate a target group.

EXAMPLE
Worksheet #1
Sam 35
Joe 37
Mary 20
Beth 41
Ted 38


Worksheet #2
Joe
Beth


Now, on the first worksheet, I want to add a third column for summing
only the target workgroup. Basically, I need a function that says if
the name in cell of ws#1 is in the array of names on ws#2, put their
hours here, otherwise 0. That way I can sum the entire column for the
target group only.

Thanks for any help you may provide.

Rita Palazzi
Senior Engineer / Global Trade Services
FedEx Express

 

[JulieD]

Hi

you can use the VLOOKUP function to do this
in B2 of the second worksheet type
=VLOOKUP(A2,Sheet1!A1:B100,2,0)
which says, look up the value of A2 in the table in Sheet1 A1:B100 and
return the information in the second column of this table where there is an
exact match.

if it is not found #NA will be returned so you use a IF statement to deal
with this
=IF(ISNA(VLOOKUP(A2,Sheet1!A1:B100,2,0)),0,VLOOKUP(A2,Sheet1!A1:B100,2,0))

Cheers
JulieD

 

[dcronje]

For your anwers to be in a third column on sheet 1 I would use thi
formula:

=(A1=Sheet2!$A$1:$A$4)*(B1)

JulieD's response put the hours on sheet 2 but from what I could se
you wanted a third column on sheet 1

 

[Rita Palazzi]

Thanks, the use of ISNA is what I needed to use VLOOKUP