**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.*