Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I have got multiple columns with info. I would like to know if there's a way that I can make questions that can reduce the columns into the criteria I am looking for. For example, Each column have different vehicles with all their specs in. Now I want to ask questions like price, year etc and eliminate the vehicles that does not fit the criteria? Thanks!
Solved by K. L. in 46 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 23/04/2018 - 09:13
Hi welcome!
User 23/04/2018 - 09:13
Excelchat Expert 23/04/2018 - 09:14
You have a question on search criteria from different columns- is that correct?
User 23/04/2018 - 09:14
Yes. I'd like to know if there's a way to filter through columns by way of elimination
User 23/04/2018 - 09:15
For example>>
Excelchat Expert 23/04/2018 - 09:15
Yes, depending on how things are set up.
User 23/04/2018 - 09:16
So now I have got 2 questions>
User 23/04/2018 - 09:17
So the in this example the bmw and the Toyota should be removed and the Mercedes only one left?
User 23/04/2018 - 09:17
Is this possible?
Excelchat Expert 23/04/2018 - 09:17
Let me review your example.
User 23/04/2018 - 09:17
The options would of course be a drop down menu
Excelchat Expert 23/04/2018 - 09:18
Ok, the criteria requires both conditions = white and 2001-correct?
User 23/04/2018 - 09:18
Excelchat Expert 23/04/2018 - 09:19
And the drop down selection results are shown in B7 and B8-correct?
User 23/04/2018 - 09:19
Excelchat Expert 23/04/2018 - 09:21
Are we allowed to add columns? What I think is the easy way to go and maintain- is a column that concats B, C, D, in this exmple and the same with critera,
Excelchat Expert 23/04/2018 - 09:23
But nor sure what you mean by only the Mercedes is left. Not really a way to delete data, Do you mean just show the selection in another part of the sheet ?
User 23/04/2018 - 09:24
I think ideally I would want to ask the questions on a different sheet and then the columns that fits in the criteria either gets highlighted or maybe shown on a separate sheet?
Excelchat Expert 23/04/2018 - 09:25
ok, I can start with some ideas and we can go from there, time permitting.
User 23/04/2018 - 09:26
Excelchat Expert 23/04/2018 - 09:26
I need to take this example to desktop , viewer is not really for construction. Please stand by.
User 23/04/2018 - 09:26
Excelchat Expert 23/04/2018 - 09:40
Ok, sorry it took a while
User 23/04/2018 - 09:40
No worries
Excelchat Expert 23/04/2018 - 09:40
[Uploaded an Excel file]
Excelchat Expert 23/04/2018 - 09:40
Here is a simple way to get started.
Excelchat Expert 23/04/2018 - 09:41
Create a key value by concat cells and use a lookup.
Excelchat Expert 23/04/2018 - 09:43
there are some other more complex ways to do this and maybe a macro or two, but in a basic chat session, I can only answer specific formula questions. Hard to give a complete solution without your actual sheet and some needs assessment.
Excelchat Expert 23/04/2018 - 09:44
I hope you understand.
User 23/04/2018 - 09:45
This is a great help. I have two more questions. The result cell, what if more there's, like for instance in this example, more than one car that match the criteria. And secondly, is there a way to make the answer in a downward row instead of horizontal?
Excelchat Expert 23/04/2018 - 09:47
Let me review
Excelchat Expert 23/04/2018 - 09:50
Yes, both are possible, but will take some time, since you now have multiple functions and needs and a bit more complex.
User 23/04/2018 - 09:51
Any recommendations where I can find these formulas?
Excelchat Expert 23/04/2018 - 09:51
I think what would be needed is your other idea of color coding
Excelchat Expert 23/04/2018 - 09:51
Use the concat functions "&" sign to create a key
Excelchat Expert 23/04/2018 - 09:52
on each row of your data
Excelchat Expert 23/04/2018 - 09:52
Then create a cell to hold the criteria to match the key, similar to what we have in this simple example
Excelchat Expert 23/04/2018 - 09:54
Then use CONDITIONAL FORMATTING- with a user defined formula. where key on each row matches the criteria. Choose your color.
Excelchat Expert 23/04/2018 - 09:54
This will identify all the possible matches in a column/row by color code.
Excelchat Expert 23/04/2018 - 09:56
Or you can add a column = FLAG. IF key = criteria ,then Y , else N. and sort desending, Y will be on top,
User 23/04/2018 - 09:57
Makes sense. I'll definitely give it a try. You have been a great help. Thank you so much!
Excelchat Expert 23/04/2018 - 09:57
Ok, thanks, agian hard to do a full solution here, but hopefully this gives you some ideas.
User 23/04/2018 - 09:58
Definitely. Thanks!

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc