=INDEX(Data1,MATCH(1,(dd!A:A=C2)*(dd!1:1=G2)*(dd!2:2=H1),0),14)

I want to pull data based on several conditions. If the cell matches a value, then I want it to check if it matches two column headers to pull the information
Mario ContrerasAsked:
Who is Participating?
 
Bill PrewCommented:
Okay, attaching a sample using the formula below.  The is an array formula so needs to be entered with CONTROL-SHIFT-ENTER,

I changed the data in Sheet1 (the percent values) just to make them unique so that it was easier to see what values were retrieved.

=INDEX(Sheet1!$A$1:$I$10,MATCH($A2,Sheet1!$A:$A,0),MATCH(1,(Sheet1!$1:$1=$B2)*(Sheet1!$2:$2=C$1),0))

Open in new window


Sample-Data.xlsx


»bp
1
 
Bill PrewCommented:
Okay, so what's the problem?  Can you supply a sample workbook showing the data layout, and where you need the formula?


»bp
1
 
Mario ContrerasAuthor Commented:
I'm trying to match the location and if the location matches then match the category and if it matches then match the section (ex. Backroom) and if so pull the data in the corresponding cell. I have larger amount of data to do this for
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Mario ContrerasAuthor Commented:
0
 
Bill PrewCommented:
I don't think you will be able to do that with the data you have shown.  The problem is that there can be multiple matches for the "Clean" and "Fresh" values.  INDEX() requires a single cell match for this approach to work.  I think you would need a User Defined Function in VBA to do this effectively.

Is this how you get the data, or did you pivot it into this format?


»bp
0
 
Mario ContrerasAuthor Commented:
unfortunately this is how i get the data
0
 
Bill PrewCommented:
Maybe I'm wrong on the MATCH(), let me play with it a bit...


»bp
1
 
Mario ContrerasAuthor Commented:
thank you!
0
 
Mario ContrerasAuthor Commented:
How do I spread the array so that i dont get an error
0
 
Bill PrewCommented:
I don't understand your question.


»bp
0
 
Mario ContrerasAuthor Commented:
So I want to spread the array in the column but when I spread it I get an error message of #N/A unless I go into each individual cell and press CSE
0
 
Bill PrewCommented:
Array formulas can be copied and pasted normally, that should work fine.

Can you upload a workbook with the problem?

Make sure the referenced data range includes values for the expanded area, etc.


»bp
0
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.

All Courses

From novice to tech pro — start learning today.