Find two values in worksheet to return one value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
Correna said:
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.
Click to expand...
 
=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
 
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))
 
Correna,


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

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
 
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
 
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..
 
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.)
 
SUMPRODUCT doesn't allow you to select a column ... you have to select a
range e.g A1:A65000.
 
Back
Top