lookup

[Jim]

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 Yoda’s 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

[Aladin Akyurek]

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 Yoda’s 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

[Aladin Akyurek]

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 Yoda’s
>> 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.

[rexmorgan]

It sound like you simply need to use the VLOOKUP function. The proper syntax is
VLOOKUP(lookup_value,table_array,col_index_num,range_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.

[QUOTE] / 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 Yoda’s 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.