=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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Bill PrewIT / Software Engineering ConsultantCommented:
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
Mario ContrerasAuthor Commented:
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Bill PrewIT / Software Engineering ConsultantCommented:
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 PrewIT / Software Engineering ConsultantCommented:
Maybe I'm wrong on the MATCH(), let me play with it a bit...


»bp
1
Mario ContrerasAuthor Commented:
thank you!
0
Bill PrewIT / Software Engineering ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
Mario ContrerasAuthor Commented:
How do I spread the array so that i dont get an error
0
Bill PrewIT / Software Engineering ConsultantCommented:
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 PrewIT / Software Engineering ConsultantCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.