Question description:
This user has given permission to use the problem statement for this
blog.
I need help. I'm trying to set up the following logical formula but it keeps giving me an error.
If cell D3 AND E3 are selected then cell J3 is the result
Solved by V. D. in 35 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
25/07/2018 - 02:51
I'm trying to set up the following formula
Excelchat Expert
25/07/2018 - 02:53
Hell, I understand that you need help with your logical formula. Please go ahead.
User
25/07/2018 - 02:54
IF cell D2 AND E2 are selected then the result will be G2
User
25/07/2018 - 02:55
I have a drop down box set up on another sheet
Excelchat Expert
25/07/2018 - 02:55
I'll need the complete details from you as I can't make the connection between D2, E2 and G2.
Excelchat Expert
25/07/2018 - 02:55
They are all just random numbers and letters to me.
Excelchat Expert
25/07/2018 - 02:55
Why will it result to INRW 0420
User
25/07/2018 - 02:55
it's a placement score
User
25/07/2018 - 02:56
so if a person ranges withing the two cells combined the result will be INRW 0420 placement
User
25/07/2018 - 02:56
test scores
Excelchat Expert
25/07/2018 - 02:56
I mean, if you are just going to give me W310-339 w/ABE 3-4 R310-350 and INRW 0420 then I can't make a logical connection between those values.
Excelchat Expert
25/07/2018 - 02:56
Can you provide more details please or better yet your actual file?
Excelchat Expert
25/07/2018 - 02:56
Just those 3 values alone does not make any sense.
User
25/07/2018 - 02:57
well i have shortened them
User
25/07/2018 - 02:57
W stands for writing
User
25/07/2018 - 02:57
score from 310 to 339 with Essay of 3 or 4
User
25/07/2018 - 02:58
AND Reading score of 310 to 350 will result in placement of INRW course
Excelchat Expert
25/07/2018 - 02:58
I'm really going to need your actual file so I can make sense of all of this.
User
25/07/2018 - 02:59
I can't it has confidential information
User
25/07/2018 - 02:59
thanks anyway
Excelchat Expert
25/07/2018 - 02:59
Can you provide more examples then?
User
25/07/2018 - 02:59
An example would be if I select bannana and apple the result would be green
User
25/07/2018 - 02:59
or the outcome/true
Excelchat Expert
25/07/2018 - 03:00
Okay, that makes more sense.
Excelchat Expert
25/07/2018 - 03:00
What are the other possible combinations and results?
Excelchat Expert
25/07/2018 - 03:01
So G is what you need to auto populate?
User
25/07/2018 - 03:01
correct
Excelchat Expert
25/07/2018 - 03:01
Okay, about how many possible combinations do you have?
User
25/07/2018 - 03:02
12
Excelchat Expert
25/07/2018 - 03:02
I'm asking because the number of possible combinations will tell us what solution to use.
Excelchat Expert
25/07/2018 - 03:02
Okay, that's quite a lot so we'll have to use a reference table.
User
25/07/2018 - 03:02
okay what is that?
Excelchat Expert
25/07/2018 - 03:03
It means we'll be using a table that will store all possible combinations so that we can lookup into that table instead of writing a very long IF() statement.
User
25/07/2018 - 03:03
oh okay
Excelchat Expert
25/07/2018 - 03:04
Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.
Excelchat Expert
25/07/2018 - 03:04
Please give me a few minutes to generate the solution for you.
User
25/07/2018 - 03:04
okay
Excelchat Expert
25/07/2018 - 03:07
A few more minutes.
Excelchat Expert
25/07/2018 - 03:11
Please download this file.
[Uploaded an Excel file]
Excelchat Expert
25/07/2018 - 03:14
Where you able to download the file?
User
25/07/2018 - 03:14
how do i do that?
User
25/07/2018 - 03:14
thank you so much
Excelchat Expert
25/07/2018 - 03:14
You can download the file by hovering your mouse on top of the Excel icon in this chat
Excelchat Expert
25/07/2018 - 03:14
Then click the down arrow button.
User
25/07/2018 - 03:15
got it
Excelchat Expert
25/07/2018 - 03:15
[Uploaded an Excel file]
User
25/07/2018 - 03:15
how did you do that
Excelchat Expert
25/07/2018 - 03:16
So as you can see as long as the combination in D and E exists anywhere in the reference sheet, it will return the right result.
Excelchat Expert
25/07/2018 - 03:16
The formula is an array formula. It's basically a multi match lookup.
Excelchat Expert
25/07/2018 - 03:16
Another approach to this is to use helper columns instead.
Excelchat Expert
25/07/2018 - 03:18
If you'll look at the document to the right, you'll see column G.
Excelchat Expert
25/07/2018 - 03:18
The formula is a simpler vlookup() solution.
Excelchat Expert
25/07/2018 - 03:18
But this requires column in the reference sheet.
Excelchat Expert
25/07/2018 - 03:19
Are you still with me?
User
25/07/2018 - 03:20
yes
User
25/07/2018 - 03:22
so i set up the table first using the vlookup
Excelchat Expert
25/07/2018 - 03:23
You set up the reference table first.
Excelchat Expert
25/07/2018 - 03:23
You write the first item in column A then second item in column B.
Excelchat Expert
25/07/2018 - 03:23
Then you use the formula in column C to generate the combination of the 2 items.
Excelchat Expert
25/07/2018 - 03:23
Then you write the expected result of the 2 combinations.
Excelchat Expert
25/07/2018 - 03:24
Then you use this formula in your actual sheet.
Excelchat Expert
25/07/2018 - 03:24
=vlookup(D2&E2,reference!C:D,2,0)
Excelchat Expert
25/07/2018 - 03:24
Where D2 and E2 are the cells where your 2 fruits are located.
User
25/07/2018 - 03:25
Got it! Thank you so much!
Excelchat Expert
25/07/2018 - 03:25
Download this file please.
[Uploaded an Excel file]
Excelchat Expert
25/07/2018 - 03:25
So you can use it as a reference for when you apply the formula to your actual file.
Excelchat Expert
25/07/2018 - 03:26
If you need help applying this to your actual file, you may want to prepare your file and remove the confidential information then post your question again.
User
25/07/2018 - 03:26
Awesome. Thank you so much!
Excelchat Expert
25/07/2018 - 03:26
That way, we can directly apply the formula to your sheet.
User
25/07/2018 - 03:26
oh cool
Excelchat Expert
25/07/2018 - 03:26
Would there be anything else that I can help you with regards to the original question?
User
25/07/2018 - 03:26
will do.
Excelchat Expert
25/07/2018 - 03:26
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating and your feedback if you think I deserve it.
User
25/07/2018 - 03:26
that's all thank again
Excelchat Expert
25/07/2018 - 03:27
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of.
Excelchat Expert
25/07/2018 - 03:27
Please do not forget to click the End Session button otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.
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.
