Solved

# Excel Lookup Value query

Posted on 2013-10-08
Medium Priority
366 Views
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
0
Question by:just4kix

LVL 81

Accepted Solution

byundt earned 2000 total points
ID: 39557186
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

LVL 25

Expert Comment

ID: 39557192
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 Closing Comment

ID: 39557237
You were spot on...exactly what I needed and it just worked perfectly.

0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.