Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Similar to sumproduct...?
Hi there, in Cells a1:z1 i have a list of items, say dogs, cats, mice etc.. (all unique) Cells a2:a100 question numbers (questions 1 to 100) Body of the table (ie a1:z100) contains the results for the questions I want to be able to extract a result. For example what was answer for question 53 for Cats Sumproduct would work if it had to sum, but I dont need to. Is it a combination of h and v lookups? Appreciate assistance!! Regards D *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Similar to sumproduct...?
Do you want an individual result, or a table of results? Can a question have
an answer for more than one category (animal?)? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Darin Kramer" wrote in message ... Hi there, in Cells a1:z1 i have a list of items, say dogs, cats, mice etc.. (all unique) Cells a2:a100 question numbers (questions 1 to 100) Body of the table (ie a1:z100) contains the results for the questions I want to be able to extract a result. For example what was answer for question 53 for Cats Sumproduct would work if it had to sum, but I dont need to. Is it a combination of h and v lookups? Appreciate assistance!! Regards D *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Similar to sumproduct...?
Your explanation is slightly confusing - e.g. you say you have
the list of items in A1:Z1, but also results of questions in A1:Z100, so there seems to be an overlap there. Anyway, I'd use a combination of MATCH and INDEX: =MATCH("Cats",A1:Z1,0) tells you which column contains Cats so =INDEX(A1:Z100, 53, MATCH("Cats",A1:Z1,0)) gives you the value from row 53 in the column with Cats at the top. You might need to adjust some of the numbers to deal with the problems I mentioned above, but something like this should do what wou want. Andrew Darin Kramer wrote: Hi there, in Cells a1:z1 i have a list of items, say dogs, cats, mice etc.. (all unique) Cells a2:a100 question numbers (questions 1 to 100) Body of the table (ie a1:z100) contains the results for the questions I want to be able to extract a result. For example what was answer for question 53 for Cats Sumproduct would work if it had to sum, but I dont need to. Is it a combination of h and v lookups? Appreciate assistance!! Regards D *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Similar to sumproduct...?
Assuming you have "cats" in F5 and 53 in F6:
=INDEX(B2:D101,MATCH(F6,A2:A101,0),MATCH(F5,B1:D1) ) HTH -- AP "Darin Kramer" a écrit dans le message de ... Hi there, in Cells a1:z1 i have a list of items, say dogs, cats, mice etc.. (all unique) Cells a2:a100 question numbers (questions 1 to 100) Body of the table (ie a1:z100) contains the results for the questions I want to be able to extract a result. For example what was answer for question 53 for Cats Sumproduct would work if it had to sum, but I dont need to. Is it a combination of h and v lookups? Appreciate assistance!! Regards D *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Similar to Q re 2 Countifs, but Sumproduct doesn't work | Excel Worksheet Functions | |||
sumproduct & Dates & similar Data | Excel Discussion (Misc queries) | |||
Sumproduct & Dates & Similar Data | Excel Worksheet Functions | |||
Help with sumproduct or similar type of function | Excel Discussion (Misc queries) | |||
SUMPRODUCT is OK for a while and later a similar formula returns 0 | Excel Worksheet Functions |