Get cell value with multiple criteria lookup

J

James Hobart

I have a relatively simple data matrix

Property Zone Acreage
1000 R 1.00
1000 C 1.20
2000 R 1.40
2000 C 0.50
2000 S 1.30
3000 R 0.75
3000 S 2.00

That contains mutiple occurances of the same property. Each occurance
of the same property is a different zone. There is one unque record
(row) for each combination of Property and Zone.

I wish to create a new table with each row being the unique property
and each column having the acreage for each unique zone. Some
combinations will result ina null value. I already have the unique
properties in the left column and I have the unique zones as the column
headers.

Property R C S
1000 1.00 1.20 null
2000 1.40 0.50 1.30
3000 0.75 null 2.00
 
R

Richard Buttrey

Hi,

With your original data in A1:C8
and your new table headings in B10:D10, and Property codes in A11:A13,
enter the following in B11 and copy across and down.

=SUMPRODUCT(($A$2:$A$8=$A11)*($B$2:$B$8=B$10)*($C$2:$C$8))

It will return 0 rather than nulls, but you could do a Find Replace
afterwards if necessary.

Alternatively you could wrap it in an if statement and do it in one
go.

=IF(SUMPRODUCT(($A$2:$A$8=$A11)*($B$2:$B$8=B$10)*($C$2:$C$8))=0,"",
SUMPRODUCT(($A$2:$A$8=$A11)*($B$2:$B$8=B$10)*($C$2:$C$8)))

(watch the word wrap)

HTH.


I have a relatively simple data matrix

Property Zone Acreage
1000 R 1.00
1000 C 1.20
2000 R 1.40
2000 C 0.50
2000 S 1.30
3000 R 0.75
3000 S 2.00

That contains mutiple occurances of the same property. Each occurance
of the same property is a different zone. There is one unque record
(row) for each combination of Property and Zone.

I wish to create a new table with each row being the unique property
and each column having the acreage for each unique zone. Some
combinations will result ina null value. I already have the unique
properties in the left column and I have the unique zones as the column
headers.

Property R C S
1000 1.00 1.20 null
2000 1.40 0.50 1.30
3000 0.75 null 2.00
Click to expand...

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
Top