Find two values in worksheet to return one value

[Correna]

I had this posted in another subject, but was getting no response. Thought I
would try it again and hopefully get another answer.

Attempting to find identical values in cells B3 & B4 located in another
worksheet titled "Density Chart" and to return the value in cell/column D.

The Density Chart values are located in column A & B and the value I want
returned, depending on the criteria entered would be found on the same row
but in column D.

Example...
Changeable Value in B3 = A123 (can also be completely alpha value and will
be different values each time the formula is used.
Changeable Value in B4 = 2.00 (always numeric value)

Density Chart information
ColumnA ColumnB ColumnC ColumnD
A123 2.00 55.555 65.555
A123 2.40 55.555 75.555
A123 2.70 55.555 70.555
B123 2.00 45.000 85.000
C123
D123
E123
etc

I have tried =if, I have tried =sum product ... at a loss

 

[Toppers]

This ..?

=SUMPRODUCT(--(A1:A4="A123"),--(B1:B4=2),--(D14))

 

[Correna]

This doesn't work, cause the values I am looking for could change. It could
be A123 2.0 one minute, then B123 2.7 the next. The values I am looking for
will continously change, but are located in the "Density Chart" worksheet in
Column A and B respectively.

 

[Toppers]

This doesn't work, cause the values I am looking for could change. It could
be A123 2.0 one minute, then B123 2.7 the next. The values I am looking for
will continously change, but are located in the "Density Chart" worksheet in
Column A and B respectively.

 

[Toppers]

=SUMPRODUCT(--('Density Chart'!A1:A4=B1),--('Density
Chart'!B1:B4=C1),--('Density Chart'!D14))

Where B1 contains your first search argument e.g A123, C1 contains the
second e.g 2.00

HTH

 

[Correna]

Tried it, doesn't work... Get a #NUM! error

This is what I put in.
=SUMPRODUCT(--('Density Chart'!A:A=B3),--('Density
Chart'!B:B=B4),--('Density Chart'!D))

 

[SteveG]

Correna,


=SUMPRODUCT((DensityChart!A1:A5000=B3)*(DensityChart!B1:B5000=B4)*(DensityChart!D15000))

As you change your values in B3 and B4, the formula will automaticall
look for those new values.

If this is the same question I answered on your other "unanswered
post, you had indicated the solution worked.


Stev

 

[SteveG]

Correna,

As Peo posted on your other post, you can not use A:A in SUMPRODUCT
you need to use A1:A5000 or some other row number.

Stev

 

[SteveG]

Correna,

So there is no confusion, you have to change all of your colum
references as I last posted whether it is A:A, B:B, C:C etc..

 

[Tristan]

Hi there,

I haven't managed how to do these SumProduct functions yet. The
following is a bit of a cheat but does work:

In cell A1 enter:

='Density Sheet'!A1&'Density Sheet'!B1

Then copy this formula down the length of the column equivalent to the
data in density sheet

Assuming that your two criteria are going into cells B+C1 then in D1
enter:

=B1&C1

Your final formula goes into D1:

=INDEX('Density Sheet'!D,MATCH(D1,A:A,0))

...and there you go.

Sorry its a dirty get around but if it works!...

Will depend on you never having a repeat value when you concatenate the
Density data. I'm assuming this will be pretty unlikely if column A has
got names, sample IDs since these will be unique (I'd avoid sticking
numbers at the very ends of the names if you can help it.)

 

[Toppers]

SUMPRODUCT doesn't allow you to select a column ... you have to select a
range e.g A1:A65000.