• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup based on 3 criteria and return an answer

M

Mark Carver

Member
I am a newbie to this site so please give me some grace if I am posing my problem in an incorrect manner. My Excel is rusty and my son asked me for some help. Basically, he has a report he needs to produce every week (3-4 lines of text, quite short). He wants to use a drop down box to input 3 conditions, the result of which looks up the pertinent cell that contains the report text. I have attached a spreadsheet that outlines the problem. Any help is GREATLY appreciated. Thank you.
 

Attachments

Hi:

May be something like this
Code: 
=INDEX($F$8:$F$10,MATCH(1,MMULT(($C$14=$C$8:$C$10)*($C$15=$D$8:$D$10)*($C$16=$E$8:$E$10),1),0),1)

Thanks
 

Attachments

Thanks. I played around with this but I can't seem to make it work. Also, my first question is "How do I set up the data table?" for all three of the variables (person, project, and week) which will return a different answer (narrative). So, how should this table be set up to also include the several narrative selections to be returned, based on the 3 conditions? Again, THANKS for the help.
 
Hi:

I assume that you will have unique narrative for each combination, if that is the case set up table with column headers, Name, Project, Week , Narrative. If it is not what you are looking for , upload a dummy data dump of what you got.

Note: if you can st up table in this format you do not need a formula a simple pivot can get what you are looking for.

Thanks
 
Hi:

There are two ways to achieve this, one is through a pivot and another is through formula. I have shown both the methods in the attached work book, the colored cell contains the formula.

Thanks
 

Attachments

Thank you. I like the formula solution the best for my application. I studied the formula and now understand it. However, I am not sure what I am doing wrong as the formula gives a #N/A result. You have been a wonderful help. May I impose on you one more time to audit the attached spreadsheet and see what is the problem?
 

Attachments

I had a thought that perhaps I need to enter the formula as an array (Ctrl-Shift-Enter). However, I am not sure on what parts I need to do so. I will experiment but I would appreciate your help (Again!). THANKS!
 
Hi ,

The formula is returning the #N/A error value because the data does not have an entry corresponding to a combination of the 3 values :

Ted , Project 3 , Week 1

Corresponding to Ted and Project 3 the value is Week 2.

Also you need to correct the Data Validation dropdowns.

Narayan
 
You must log in or register to reply here.
Back
Top