lookup
 

Hello and Thanks for the help.

I have three sheets I am working with.

Sheet one has the store name and number listed (reference link from sheet 2)
and a place to hold the answer that one of you Excel Yodas provide.

Sheet two shows the store name and store number is two columns. For
example: StoreA appears in a1 and its store number (#1) appears in b1.

Sheet three has the data that I am dumping into the workbook. The data of
interest is the store number (column G) and the prompt number (column J).
The prompt number is the number that a caller calls to prompt for sales or
service (1 for sale, two for service).

What I would like is a formula that refers to the store name and number I
have on sheet one, then counts the prompts (1 for sale, 2 for service) by
store number on sheet three.

Just counting the one or two if it matches by store name.

Thanks

lookup
 

Assuming that you want to count in a record when prompt is either 1 or 2..

=SUMPRODUCT(--(StoreNameRange=StoreName),--(StoreNumberRange=StoreNumber),--ISNUMBER(MATCH(PromptRange,{1,2},0)))


Jim wrote:
Hello and Thanks for the help.

I have three sheets I am working with.

Sheet one has the store name and number listed (reference link from sheet 2)
and a place to hold the answer that one of you Excel Yodas provide.

Sheet two shows the store name and store number is two columns. For
example: StoreA appears in a1 and its store number (#1) appears in b1.

Sheet three has the data that I am dumping into the workbook. The data of
interest is the store number (column G) and the prompt number (column J).
The prompt number is the number that a caller calls to prompt for sales or
service (1 for sale, two for service).

What I would like is a formula that refers to the store name and number I
have on sheet one, then counts the prompts (1 for sale, 2 for service) by
store number on sheet three.

Just counting the one or two if it matches by store name.

Thanks

lookup
 

Make that:

=SUMPRODUCT(--(StoreNumberRange=StoreNumber),--ISNUMBER(MATCH(PromptRange,{1,2},0)))


Aladin Akyurek wrote:
Assuming that you want to count in a record when prompt is either 1 or 2..

=SUMPRODUCT(--(StoreNameRange=StoreName),--(StoreNumberRange=StoreNumber),--ISNUMBER(MATCH(PromptRange,{1,2},0)))



Jim wrote:

Hello and Thanks for the help.

I have three sheets I am working with.

Sheet one has the store name and number listed (reference link from
sheet 2) and a place to hold the answer that one of you Excel Yodas
provide.

Sheet two shows the store name and store number is two columns. For
example: StoreA appears in a1 and its store number (#1) appears in b1.

Sheet three has the data that I am dumping into the workbook. The
data of interest is the store number (column G) and the prompt number
(column J). The prompt number is the number that a caller calls to
prompt for sales or service (1 for sale, two for service).

What I would like is a formula that refers to the store name and
number I have on sheet one, then counts the prompts (1 for sale, 2 for
service) by store number on sheet three.

Just counting the one or two if it matches by store name.

Thanks

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

lookup
 

It sound like you simply need to use the VLOOKUP function. The proper
syntax is
VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup). Where

lookup_value = The value to be looked up in the first column of the
lookup table
table_array = The range that contains the lookup table
col_index_num = The column number within the table from which the
matching value is returned

For example if I had a table in worsheet 3 with the store name in
column G and the store number in column H your formula would look
something like this

VLOOKUP(store name,table location and range, 8,false)
or
VLOOKUP(A2,Sheet3!A1:J100,8,false)
this would return the value in column H or the 8th column in sheet 3
that has a name match for the value in cell A2.

I hope this helps. I have included a very basic idea of how to use this
function.

/ Sheet one has the store name and number listed (reference link from
sheet 2)
and a place to hold the answer that one of you Excel Yodas provide.

Sheet two shows the store name and store number is two columns. For
example: StoreA appears in a1 and its store number (#1) appears in b1.

Sheet three has the data that I am dumping into the workbook. The data
of
interest is the store number (column G) and the prompt number (column
J).
The prompt number is the number that a caller calls to prompt for sales
or
service (1 for sale, two for service).

What I would like is a formula that refers to the store name and number
I
have on sheet one, then counts the prompts (1 for sale, 2 for service)
by
store number on sheet three.

Just counting the one or two if it matches by store name.


+-------------------------------------------------------------------+
|Filename: vlookup_help.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3913 |
+-------------------------------------------------------------------+

--
rexmorgan
------------------------------------------------------------------------
rexmorgan's Profile: http://www.excelforum.com/member.php...o&userid=28167
View this thread: http://www.excelforum.com/showthread...hreadid=476950