# Excel Lookup Value query

Hi,

I have a simple row/column data set and I am looking to aggregate the values into a summarised view table. The values which are true are marked with an 'x' character.

A      B      C      D
Apples      x
Apples            x
Apples                  x

This is what it is currently returning (various INDEX/MATCH, SUMIFS, SUMPRODUCT combinations)
A      B      C      D
Apples      x      0      0      0

This is what I need a formula of some sort to return
A      B      C      D
Apples      x      x      x

Thanks
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mechanical EngineerCommented:
I wish that you had posted a sample file, but I think you want a formula like:
=IF(COUNTIFS(\$A:\$A,\$K2,B:B,"X")>0,"x","")

In the above formula, the fruit names are in column A. The "X" indicators are in columns B:E. Cell K2 in the results table contains "Apples".
FruitPivotQ28261756.xlsx
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
If data are in column A:D, and result in column G:K with the names (Apples etc.) in column G and A,B,C and D in row 1 of column H:K, then the formula for H2 is
=IF(COUNTIFS(\$A:\$A,\$G2,B:B,"x")>0,"x","")
Copy to other columns.
The formula set an x if 1 x or more is found for names (Apples etc.) in column B
0
Author Commented:
You were spot on...exactly what I needed and it just worked perfectly.