Question description:
This user has given permission to use the problem statement for this
blog.
Hello good afternoon, I have a problem with excel. I need a formula to compare data between 3 columns and give me the result in a fourth column
Solved by X. B. in 58 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
26/02/2018 - 04:06
Welcome to excel got it pro.
Excelchat Expert
26/02/2018 - 04:06
I'm here to help you today.
Excelchat Expert
26/02/2018 - 04:06
I understand you want to compare 3 columns then return result in a fourth column.
Excelchat Expert
26/02/2018 - 04:07
Please tell me details.
User
26/02/2018 - 04:07
ok
Excelchat Expert
26/02/2018 - 04:08
I'm waiting for the details.....
User
26/02/2018 - 04:09
im on it im spanish is har to me translate
User
26/02/2018 - 04:09
Xd
Excelchat Expert
26/02/2018 - 04:09
take your time. I'm in no rush. :)
User
26/02/2018 - 04:12
in the first column i have 5 options, if the option is 1, I have to compare it with column 2 (that has 5 options) and if that is correct, compare column 2 with column 3 that has 9 options, in case the correct options match, the result of Column 4 would be ok
Excelchat Expert
26/02/2018 - 04:14
Thanks, its great, I now know what you are looking for. But I still need some more. Sample data on the preview window would be great, it would be even better if you can share the file.
User
26/02/2018 - 04:14
ok
Excelchat Expert
26/02/2018 - 04:14
Great..
User
26/02/2018 - 04:18
ok if columm 1 is "1. MEJOR TARIFA CONVERGENTE EN OTRO OPERADOR" and columm 2 is one of "YA TIENE CONVERGENTE CON NOSOTROS
NO TIENE CONVERGENTE CON NOSOTROS
NO TIENE COBERTURA CON NOSOTROS
TERMINAL
TV" and columm 3 is one of "No hay cobertura
Cobertura ADSL
Tiene permanencia con otro operador
Mejor precio competencia
Pidió oferta antes y no se la dimos ahora es tarde
Oferta de TV competencia
Mejor oferta segundas lineas
Terminal
No acepta el compromiso de permanencia nuestro" is correct
Excelchat Expert
26/02/2018 - 04:19
I think I understand it now.
Excelchat Expert
26/02/2018 - 04:20
If correct options don't match, then what do you want?
User
26/02/2018 - 04:21
"error"
Excelchat Expert
26/02/2018 - 04:21
Thanks, Please gimme a few minutes.
User
26/02/2018 - 04:21
ok
Excelchat Expert
26/02/2018 - 04:21
If you are asked whether you need more time or not, Please select YES.
User
26/02/2018 - 04:21
how it cost?
User
26/02/2018 - 04:22
how much?
Excelchat Expert
26/02/2018 - 04:22
The extension for a unresolved session is free. Meaning, if I can't solve the problem in next 4 minutes you will get extra 20 minutes for free.
Excelchat Expert
26/02/2018 - 04:23
Is it clear?
User
26/02/2018 - 04:23
Is it costing me money now? I need to know that first
Excelchat Expert
26/02/2018 - 04:23
I don't know that, if you have subscribed to our site then yes it is costing you.
User
26/02/2018 - 04:25
but then how do they charge me? I have not indicated any method of payment nor do I have an amount to pay
Excelchat Expert
26/02/2018 - 04:26
okay, then you must have earned a free session.
Excelchat Expert
26/02/2018 - 04:26
In that case you are not being charged. :)
User
26/02/2018 - 04:26
:) ty
Excelchat Expert
26/02/2018 - 04:26
Congratulation on your free session. I will now continue working on the solution. Stay with me.
User
26/02/2018 - 04:27
ok
Excelchat Expert
26/02/2018 - 04:29
Stay with me please, i will let you know when I'm done.
User
26/02/2018 - 04:30
ok ok
Excelchat Expert
26/02/2018 - 04:30
Thanks!
Excelchat Expert
26/02/2018 - 04:37
I have solved the problem..
Excelchat Expert
26/02/2018 - 04:37
=IF(AND(A1='Case 1'!$A$1,ISNUMBER(MATCH(B1,'Case 1'!$B$1:$B$5,0)),ISNUMBER(MATCH(C1,'Case 1'!$C$1:$C$9,0))),"ok","error")
Excelchat Expert
26/02/2018 - 04:37
Here is the formula I have used to solve the problem and applied it in column D.
Excelchat Expert
26/02/2018 - 04:38
Please check and let me know.
User
26/02/2018 - 04:45
lets see
Excelchat Expert
26/02/2018 - 04:47
good to have you back, A reminder, you won't be able to extend any more, our standard session time is 20 minutes and you can extend that for 1 hour at most if the original problem isn't resolved withing the standard 20 minutes period.
User
26/02/2018 - 04:49
this case in yellowshould be ok?
Excelchat Expert
26/02/2018 - 04:49
yes, it should be OK.
Excelchat Expert
26/02/2018 - 04:50
any problem?
User
26/02/2018 - 04:50
no no no
Excelchat Expert
26/02/2018 - 04:51
I'm sorry, what do you mean by "no no no".
User
26/02/2018 - 04:52
It's a big part of what I had in mind, I do not want to take away more from you and I thank you very much for your help
User
26/02/2018 - 04:52
I just need to know how to concatenate so that I do the same in all 7 cases
Excelchat Expert
26/02/2018 - 04:52
So, I guess my service was a help for what you were looking for right?
Excelchat Expert
26/02/2018 - 04:52
Okay..
Excelchat Expert
26/02/2018 - 04:53
=IF(AND(A1='Case 1'!$A$1,ISNUMBER(MATCH(B1,'Case 1'!$B$1:$B$5,0)),ISNUMBER(MATCH(C1,'Case 1'!$C$1:$C$9,0))),"ok","error") this formula I have wrote for 1 case, right?
User
26/02/2018 - 04:53
yes
Excelchat Expert
26/02/2018 - 04:54
It is called IF formula, it checks a logic, then show results. The syntax is =IF(logic-test, value-if-true, value-if-false)
Excelchat Expert
26/02/2018 - 04:54
So, here the logic test is AND(A1='Case 1'!$A$1,ISNUMBER(MATCH(B1,'Case 1'!$B$1:$B$5,0)),ISNUMBER(MATCH(C1,'Case 1'!$C$1:$C$9,0)))
Excelchat Expert
26/02/2018 - 04:55
there are three conditions and each of these conditions needs to be correct.
User
26/02/2018 - 04:56
i understand
Excelchat Expert
26/02/2018 - 04:56
conditions are column A matches first column. the formula for that is A1='Case 1'!$A$1.
Excelchat Expert
26/02/2018 - 04:57
2nd logic is column B matches 2nd column , logic is ISNUMBER(MATCH(B1,'Case 1'!$B$1:$B$5,0))
Excelchat Expert
26/02/2018 - 04:57
formula for 3rd logic is ISNUMBER(MATCH(C1,'Case 1'!$C$1:$C$9,0))
Excelchat Expert
26/02/2018 - 04:58
Now, we need all these 3 to be correct. Hence we concatenate the three logic with AND function like this... AND(A1='Case 1'!$A$1,ISNUMBER(MATCH(B1,'Case 1'!$B$1:$B$5,0)),ISNUMBER(MATCH(C1,'Case 1'!$C$1:$C$9,0)))
Excelchat Expert
26/02/2018 - 04:58
Does it make sense?
Excelchat Expert
26/02/2018 - 04:59
Then we are telling excel if this is true show "ok", otherwise show "error"
Excelchat Expert
26/02/2018 - 05:00
any problem?
User
26/02/2018 - 05:00
I understand perfectly what you want to tell me, you are a great professional. What I mean, is that if instead of being case 1, is the case two, or 3 or 4, as it could be formulated, or what function I need so that in a single formula, I verify that it is okay in all cases
Excelchat Expert
26/02/2018 - 05:01
If you've understood the explanation, then the next part is easy.
User
26/02/2018 - 05:01
if it's too much, I understand it, just to know the function I should use investigate, you've done a great job
Excelchat Expert
26/02/2018 - 05:02
No no.
Excelchat Expert
26/02/2018 - 05:02
Create similar test for each case, then concatenate all those cases with OR function.
User
26/02/2018 - 05:03
ok ok great ¡¡¡¡¡ you are incredible thank you too much
Excelchat Expert
26/02/2018 - 05:03
AND/OR function is the same, AND means all logic should be TRUE, OR mean either one of the logic is TRUE.
Excelchat Expert
26/02/2018 - 05:03
Thank you
User
26/02/2018 - 05:04
i hope you have a really nice day
Excelchat Expert
26/02/2018 - 05:04
I try my best.
Excelchat Expert
26/02/2018 - 05:04
Same to you.
User
26/02/2018 - 05:04
bye
Excelchat Expert
26/02/2018 - 05:04
Thanks for trying out excel got it pro.
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.
