Find two values in worksheet to return one value
 

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

Find two values in worksheet to return one value
 

This ..?

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

"Correna" wrote:

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

Find two values in worksheet to return one value
 

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" wrote:

This ..?

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

"Correna" wrote:

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

Find two values in worksheet to return one value
 

"Correna" wrote:

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" wrote:

This ..?

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

"Correna" wrote:

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

Find two values in worksheet to return one value
 

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

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

HTH

"Correna" wrote:

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" wrote:

This ..?

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

"Correna" wrote:

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

Find two values in worksheet to return one value
 

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:D))

"Toppers" wrote:



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

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

HTH

"Correna" wrote:

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" wrote:

This ..?

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

"Correna" wrote:

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

Find two values in worksheet to return one value
 

Correna,


=SUMPRODUCT((DensityChart!A1:A5000=B3)*(DensityCha rt!B1:B5000=B4)*(DensityChart!D1:D5000))

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

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


Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=539006

Find two values in worksheet to return one value
 

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.

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=539006

Find two values in worksheet to return one value
 

Correna,

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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=539006

Find two values in worksheet to return one value
 

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: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.)


--
Tristan
------------------------------------------------------------------------
Tristan's Profile: http://www.excelforum.com/member.php...o&userid=34061
View this thread: http://www.excelforum.com/showthread...hreadid=539006

Find two values in worksheet to return one value
 

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

"Correna" wrote:

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:D))

"Toppers" wrote:



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

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

HTH

"Correna" wrote:

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" wrote:

This ..?

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

"Correna" wrote:

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