MATCH or VLOOKUP or ??

[SMAX]

Hello Good People (and bad ones too,

I have a master list of all Police *call types * in a column.
Example:

CALL_TYP
1040
1045
1050
1060
1070A

Then I have a list containing the actual number of calls per CALL_Type
however, not all call types have data. (notice that call type 1050 i
missing because it does not have any data) Example:

CALL_TYPE [/B] *#_OF_CALL
1040 13
1045 9
1060 2
1070A 6

What I want to be able to do is populate the master call list with dat
from the actual number of calls data. I need a formula that will looku
a call type on the master list, then look up a range of call types on m
actual number of calls list and then if it finds a match, return wit
the # of calls next to the call type.

Man, I hope this make sense to someone other than me. The fina
report would look something (or exactly) like this:

CALL_TYPE * #_OF_CALL
1040 13
1045 9
1050 0
1060 2
1070A 6

If anyone has any ideas, or even a starting point where I could look,
would be super grateful.

Thanks again for even reading this post.

Gu

 

[duane]

something like

=if(isna(match(call,actual call list,1)),0,vlookup(call,actual cal
list,2))

where call is each item on the master list and actual call list is th
range of actual calls - this assumes the # of calls is 1 column to th
right of the call i

 

[Peo Sjoblom]

A simple sumif should do, in an adjacent cell to the master list use
(assume master list data starts in A2 with 1040)

=SUMIF(Sheet2!$A$2:$A$100,A2,Sheet2!$B$2:$B$100)

where Call B table is Sheet2!A1:B100 including headers, now copy down the
formula
and it will sum what's in column B on sheet 2 for each call type

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[mike]

I do not get anyone of this answers, did they work? I do
not think so