Excel formula - looks up two values?

[Edinburgh]

Hello!

I need to write a formula that allows me to look up two values in a series
of vertical lists and then returns a total value (sumif) from the same table
array that statisfies both criteria.

Something like a vlookup and an AND and a SUMIF althogether if there is such
a beast?

Thanks

 

[John C]

Try sumproduct.

=SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100))
where column C is the value you want returned.

 

[Edinburgh]

Thank you John C. Unfortunately it didnt work? I'm not sure why not? Do
you have any other ideas??

 

[David Biddulph]

With all due respect, "it didnt work" isn't a desperately useful description
of your problem, and (short of clairvoyance) it doesn't give contributors to
the group a great deal of hope of helping you to identify what you've done
wrong.

Exactly what formula did you use? What data values were in the cells
leading into that formula? What result did you get? What result did you
expect?

 

[Edinburgh]

Hi David,

I realise it is unsatisfactory and I apologise. I'm not a technically
minded person so this is all very mind-boggling to me!

I have a sheet of data which details all the hours recorded by all employees
who each have a 'grade' attached to them and their time is also recorded
against the projects they are working on in that particular period. What I
wanted was a formua that allowed me to return the total of hours worked by
project but also by grade.

I tried the exact formula John suggested
=SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100))
I seem to have found a suitable solution that should allow me to get the
results that I want, but thank you for your time anyway.

Edinburgh

 

[David Biddulph]

With that formula, you would need to have defined names for value1 and
value2. What John presumably intended you to do was to replace value1 and
value2 in the formula either by numbers which you were looking for, or by
text strings, or by a reference to the cells in which the values you are
looking for would be found.

So perhaps
=SUMPRODUCT(--($A$1:$A$100=42),--($B$1:$B$100=99),($C$1:$C$100)) or
=SUMPRODUCT(--($A$1:$A$100="value1"),--($B$1:$B$100="value2"),($C$1:$C$100))
or
=SUMPRODUCT(--($A$1:$A$100=D$1),--($B$1:$B$100=E$1),($C$1:$C$100))

 

[Marie Bayes]

Hi David

I'd like to jump in on this answer if I may? I want something similar to
Edinburgh except the value I want to return from column C is a text value.
Is there a formula that I can use that will do this for me, similar to
sumproduct but obviously not producing sums...???

 

[David Biddulph]

If you are just looking at an individual row, and not producing sums, then
it sounds as if you just want
=IF(AND(A1=42,B1=99),C1,"") or
=IF(AND(A1="value1",B1="value2"),C1,"") or
=IF(AND(A1=D$1,B1=E$1),C1,"")

In each case I have assumed that you want a blank result if the conditions
aren't satisfied. If you want a different response, put it instead of the
"" towards the end of the IF(...) formula.

 

[Marie Bayes]

Hi David

That's almost what I require, except I think I need it to do a lookup. Let
me explain in a bit more detail! Column A has a reference number, column B
has a question and column C has the answer to the question in Column B.
Column A reference number refers to a set of data, so for each reference
number in Column A there are 17 Questions in Column B, all with a unique
answer in Column C. I'm trying to transpose the data so that I get one set
of data in one row. I'm hoping you can help as this spreadsheet is huge!!!

Here's an example of the data:
COLUMN A
COLUMN B COLUMN C
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Accommodation type
required Twin
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Any special dietary
requirements No
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Any special dietary
requirements No
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 County Midlothian
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 County Midlothian
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Date of Birth DATE1
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Date of Birth Date2
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Do you have any
disabilities or medical conditions that we should be aware of No
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Do you have any
disabilities or medical conditions that we should be aware of No
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Do you require any
special access No
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Do you require any
special access No
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Email email address 1
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Email email address 2
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Emergency contact
details Cont 123
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Emergency contact
details Cont 124
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 First Name Patricia
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 First Name Rosaleen
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 First name as in
passport Patricia Anne
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 First name as in
passport Rosaleen
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Home Address Address 1
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Home Address Address 2
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Home contact
number Number 123
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Home contact
number Number 124
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Known as Patti
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Known as Rosie
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Mobile Number Another
Number 123
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Mobile Number Another
Number 124
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Nationality British
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Nationality British
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Passport Expiry
date Oct 2016
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Passport Expiry
date May 2014
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Passport Number Pass No
123
41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Passport Number Pass No
124

 

[Marie Bayes]

I thought it may help to change the formula to
=IF(AND(F:F=$A:$A,G$1=$B:$B),$C:$C,"") and use CTRL/Shift to make it an
array, but still not returning the results I require...

 

[David Biddulph]

If I understand correctly what you want, sort your 3 columns (ABC) together
by column A, so that you have your 17 sets of identical column A values in
consecutive rows, with the 17 sets of column B questions and column C
answers alongside them.
If what you want is to see the column A reference (A1) in D1 then the first
column B question (B1) in E1, followed by its answer (C1) in F1, then the
second question (B2) in G1, and its answer (C2) in H1, and so on, use the
following formulae.
In D1
=OFFSET($A$1,(ROW(A1)-1)*17,0)
In E1
=OFFSET($B$1,(COLUMN(E1)-COLUMN($E$1))/2+(ROW(A1)-1)*17,MOD(COLUMN(E1)-COLUMN($E1),2))
Copy E1 across as far as AL1, which should be the column for answer C17
Then copy D1:AL1 down for as many rows as there are sets of questions.

 

[Marie Bayes]

Hi David

I love what you've done there, however, i'm sorry but it's not what I require:
I have, in column G, used filter special to create a list of unique
reference numbers from column A, I have then done the same with column B, but
transposed these so that the unique 'questions' now appear in columns H to
AI, what I need is to lookup the 'answers' from Column C, that relate to the
reference number G1 and the question H1, then I1, then J1 etc
Here's a simpler example of what I have in the spreadsheet A:C
A B C
Ref1 Q1 AnswerABC
Ref2 Q1 AnswerDEF
Ref1 Q2 AnswerGHI
Ref3 Q1 AnswerJKL
Ref1 Q3 AnswerMNO
Ref2 Q2 AnswerPQR
Ref3 Q3 AnswerSTU

Then I want to see this like so:

H I J K
REFER Q1 Q2 Q3
Ref1 AnswerABC AnswerGHI AnswerMNO
Ref2 AnswerDEF AnswerPQR
Ref3 AnswerJKL AnswerSTU

I've produced Column H, my unique reference nos, I've produced I1:AI1, my
questions, now I need to match up the references and questions to the
answers, phew, I think I might have just managed to explain it! I hope that
makes sense and thanks for listening!