Excel Lookup Value query

Posted on 2013-10-08
Medium Priority
Last Modified: 2013-10-08

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      

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:

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".
LVL 25

Expert Comment

by:Ejgil Hedegaard
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
Copy to other columns.
The formula set an x if 1 x or more is found for names (Apples etc.) in column B

Author Closing Comment

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

Thanks to all that answered

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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.

Join & Write a Comment

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question