Excel - IF Function Problem - Expert Solution

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.

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.