Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I use array references in VLOOKUP()?
I have several rows in spreadsheet_1, each row with an employee name followed
by four columns of numbers ranging in value from 1 to 7. Following these numbers is a SCORE I want to calculate, and it is in this column that I want to place the formula I am asking about. NAME CAT1 CAT2 CAT3 CAT4 Joe Smith 5 4 5 6 sum_of_values Harry Houdini 4 4 3 3 sum_of_values Jane Doe 1 2 3 4 sum_of_values In a second spreadsheet, spreadsheet_2, I have a 7 x 4 array of values whose rows correspond to the CAT score in the first spreadsheet, and whose columns correspond to the value associated with that CAT score. SCORE CAT1 CAT2 CAT3 CAT4 1 5 7 2 10 2 10 14 4 20 3 15 21 6 30 4 20 28 8 40 5 25 35 10 50 6 30 42 12 60 7 35 49 14 70 I want the formula in the cells labeled "sum_of_values" in spreadsheet_1 to use the CAT scores in the row for a person to look up the asociated value for a score and CAT column in spreadsheet_2, do that for each score and value, and add the result. For example, for Joe Smith his "sum_of_values" would be: CAT1 score = 5, associated value = 25 CAT2 score = 4, associated value = 28 CAT3 score = 5, associated value = 10 CAT4 score = 6, associated value = 60 sum_of_values = 123 I tried using SUM(VLOOKUP(spreadsheet_1!b2:e2,spreadsheet_2!a2:e 8,{2,3,4,5}), and entering this as an array formula (CTRL-SHIFT-ENTER), but the VLOOKUP() function only evaluates the first argument once, as a "5", and uses that value for all the subsequent lookups corresponding to the array constant "{2,3,4,5}". Therefore, I get an array summed value, but it calculates as if Joe Smith scored all "5"s rather than "5 4 5 6". |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I use array references in VLOOKUP()?
Try this:
Entered in F2 of sheet1: =SUMPRODUCT((Sheet2!A$2:A$8=B2:E2)*(B$1:E$1=Sheet2 !B$1:E$1)*Sheet2!B$2:E$8) Copy down as needed. -- Biff Microsoft Excel MVP "tsimkus" wrote in message ... I have several rows in spreadsheet_1, each row with an employee name followed by four columns of numbers ranging in value from 1 to 7. Following these numbers is a SCORE I want to calculate, and it is in this column that I want to place the formula I am asking about. NAME CAT1 CAT2 CAT3 CAT4 Joe Smith 5 4 5 6 sum_of_values Harry Houdini 4 4 3 3 sum_of_values Jane Doe 1 2 3 4 sum_of_values In a second spreadsheet, spreadsheet_2, I have a 7 x 4 array of values whose rows correspond to the CAT score in the first spreadsheet, and whose columns correspond to the value associated with that CAT score. SCORE CAT1 CAT2 CAT3 CAT4 1 5 7 2 10 2 10 14 4 20 3 15 21 6 30 4 20 28 8 40 5 25 35 10 50 6 30 42 12 60 7 35 49 14 70 I want the formula in the cells labeled "sum_of_values" in spreadsheet_1 to use the CAT scores in the row for a person to look up the asociated value for a score and CAT column in spreadsheet_2, do that for each score and value, and add the result. For example, for Joe Smith his "sum_of_values" would be: CAT1 score = 5, associated value = 25 CAT2 score = 4, associated value = 28 CAT3 score = 5, associated value = 10 CAT4 score = 6, associated value = 60 sum_of_values = 123 I tried using SUM(VLOOKUP(spreadsheet_1!b2:e2,spreadsheet_2!a2:e 8,{2,3,4,5}), and entering this as an array formula (CTRL-SHIFT-ENTER), but the VLOOKUP() function only evaluates the first argument once, as a "5", and uses that value for all the subsequent lookups corresponding to the array constant "{2,3,4,5}". Therefore, I get an array summed value, but it calculates as if Joe Smith scored all "5"s rather than "5 4 5 6". |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I use array references in VLOOKUP()?
I think this should be enough,
=SUMPRODUCT(--(Sheet2!A$2:A$8=B2:E2),Sheet2!B$2:E$8) Trying =SUM(VLOOKUP(b2:e2,sheet2!a2:e8,{2,3,4,5})) shows the inconsistency in evaluating some array formulas: - If it's array-entered in one cell it returns 120 - the result of fixing the first argument to B2 and using {2,3,4,5}. - If it's array entered in more than one cell it gives 100 - the result of using B2:E2 but fixing the third argument to {2}. "T. Valko" wrote: Try this: Entered in F2 of sheet1: =SUMPRODUCT((Sheet2!A$2:A$8=B2:E2)*(B$1:E$1=Sheet2 !B$1:E$1)*Sheet2!B$2:E$8) Copy down as needed. -- Biff Microsoft Excel MVP "tsimkus" wrote in message ... I have several rows in spreadsheet_1, each row with an employee name followed by four columns of numbers ranging in value from 1 to 7. Following these numbers is a SCORE I want to calculate, and it is in this column that I want to place the formula I am asking about. NAME CAT1 CAT2 CAT3 CAT4 Joe Smith 5 4 5 6 sum_of_values Harry Houdini 4 4 3 3 sum_of_values Jane Doe 1 2 3 4 sum_of_values In a second spreadsheet, spreadsheet_2, I have a 7 x 4 array of values whose rows correspond to the CAT score in the first spreadsheet, and whose columns correspond to the value associated with that CAT score. SCORE CAT1 CAT2 CAT3 CAT4 1 5 7 2 10 2 10 14 4 20 3 15 21 6 30 4 20 28 8 40 5 25 35 10 50 6 30 42 12 60 7 35 49 14 70 I want the formula in the cells labeled "sum_of_values" in spreadsheet_1 to use the CAT scores in the row for a person to look up the asociated value for a score and CAT column in spreadsheet_2, do that for each score and value, and add the result. For example, for Joe Smith his "sum_of_values" would be: CAT1 score = 5, associated value = 25 CAT2 score = 4, associated value = 28 CAT3 score = 5, associated value = 10 CAT4 score = 6, associated value = 60 sum_of_values = 123 I tried using SUM(VLOOKUP(spreadsheet_1!b2:e2,spreadsheet_2!a2:e 8,{2,3,4,5}), and entering this as an array formula (CTRL-SHIFT-ENTER), but the VLOOKUP() function only evaluates the first argument once, as a "5", and uses that value for all the subsequent lookups corresponding to the array constant "{2,3,4,5}". Therefore, I get an array summed value, but it calculates as if Joe Smith scored all "5"s rather than "5 4 5 6". |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prevent cell/array references from changing when altering/moving thecell/array | Excel Discussion (Misc queries) | |||
VLOOKUP variable array references | Excel Discussion (Misc queries) | |||
Display an array of references | Excel Worksheet Functions | |||
How to copy an array without changing relative cell references? | Excel Worksheet Functions | |||
How do I paste horizontal references in large array | Excel Worksheet Functions |