Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another way to lookup data | Excel Worksheet Functions | |||
lookup more than one cell | Excel Worksheet Functions | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |