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

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

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

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