lookup

[Guest]

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)))

 

[Aladin Akyurek]

Make that:

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

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)))

--

[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 prope
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 th
lookup table
table_array = The range that contains the lookup table
col_index_num = The column number within the table from which th
matching value is returned

For example if I had a table in worsheet 3 with the store name i
column G and the store number in column H your formula would loo
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
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 thi
function.

/ Sheet one has the store name and number listed (reference link fro
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 dat
of
interest is the store number (column G) and the prompt number (colum
J).
The prompt number is the number that a caller calls to prompt for sale
or
service (1 for sale, two for service).

What I would like is a formula that refers to the store name and numbe
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
+-------------------------------------------------------------------