Question description:
This user has given permission to use the problem statement for this
blog.
Hi. If i want to write a formula that reverts a scale in example column B, to the opposite in column C. Ex. all value 5 to 1, all value 4 to 2, all value 3 to 3, all value 2 to 4 and all value 1 to 5. Is it possible to use the IF function, or another function to do this?
Solved by V. A. in 18 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
21/04/2018 - 11:23
Welcome to got it pro.
Excelchat Expert
21/04/2018 - 11:23
I'm here to help you today.
Excelchat Expert
21/04/2018 - 11:23
I need some example to fully understand the problem.
Excelchat Expert
21/04/2018 - 11:24
Thanks, I see you are putting together some example in the preview sheet.
User
21/04/2018 - 11:24
The scale in the document preview, i want to make it the opposite in the C column
User
21/04/2018 - 11:24
Like, all 1's turn to 5, all 2's turn to 4
User
21/04/2018 - 11:24
I have tried the IF function, but i can only do one number at a time
Excelchat Expert
21/04/2018 - 11:25
For example, you want C2 to be 4? C3 should be 2?
User
21/04/2018 - 11:25
C2 should turn to 5
User
21/04/2018 - 11:26
The scale is from 1 to 5, and it should be the opposite value in the C column
User
21/04/2018 - 11:26
Like the example you are writing yes
Excelchat Expert
21/04/2018 - 11:26
Thanks.
Excelchat Expert
21/04/2018 - 11:26
For confirming it.
Excelchat Expert
21/04/2018 - 11:27
I need a couple of minutes.
Excelchat Expert
21/04/2018 - 11:27
Please check column C now.
User
21/04/2018 - 11:27
It's correct
User
21/04/2018 - 11:27
What formula do you use?
Excelchat Expert
21/04/2018 - 11:27
Glad to hear.
Excelchat Expert
21/04/2018 - 11:27
=VLOOKUP(B2,$F$2:$G$6,2,false)
Excelchat Expert
21/04/2018 - 11:28
this is the formula and we need assistance of the table.
Excelchat Expert
21/04/2018 - 11:28
Does it make sense?
User
21/04/2018 - 11:29
No, sorry
User
21/04/2018 - 11:29
I see the formula
Excelchat Expert
21/04/2018 - 11:29
okay, explaining it now.
User
21/04/2018 - 11:30
I've tried to copy this to my excel sheet, but it doesn't work
Excelchat Expert
21/04/2018 - 11:30
alright, you also need to copy the table and update the formula accordingly.
Excelchat Expert
21/04/2018 - 11:31
if you give a some time, I will explain how the formula works.
Excelchat Expert
21/04/2018 - 11:31
let me know when you are ready.
User
21/04/2018 - 11:31
Okay, just got what you meant by assistance
User
21/04/2018 - 11:31
I will try, but you explain
Excelchat Expert
21/04/2018 - 11:31
ready?
User
21/04/2018 - 11:32
Yes
Excelchat Expert
21/04/2018 - 11:33
in the table, I have listed the numbers and their corresponding opposite values that you need.
Excelchat Expert
21/04/2018 - 11:34
Now using =VLOOKUP(B2,$F$2:$G$6,2,false) formula we are searching value of B2 in F2:G6 range. as mentioned earlier G2:G6 range contains the number and their corresponding opposite numbers.
Excelchat Expert
21/04/2018 - 11:35
make sense?
User
21/04/2018 - 11:35
Yes
Excelchat Expert
21/04/2018 - 11:36
Now, you see a 2 in the formula? it means we want to return a result against matched values from the 2nd column of the F2:G6 range.
Excelchat Expert
21/04/2018 - 11:36
make sense?
Excelchat Expert
21/04/2018 - 11:36
I see what you are doing, that's great work man.
User
21/04/2018 - 11:36
Yes
User
21/04/2018 - 11:36
Thank you!
User
21/04/2018 - 11:37
Very much
Excelchat Expert
21/04/2018 - 11:37
There are numerous benefit of using this VLOOKUP formula over IF formula.
Excelchat Expert
21/04/2018 - 11:38
The most notable advantage is when you need to change a number and its corresponding number, you just have to change it in the table.
Excelchat Expert
21/04/2018 - 11:38
No need to change anything in the formula.
User
21/04/2018 - 11:38
I see
Excelchat Expert
21/04/2018 - 11:38
Ain't it beautiful?
User
21/04/2018 - 11:38
Excel is beautiful!
Excelchat Expert
21/04/2018 - 11:39
You are spot on in saying that.
Excelchat Expert
21/04/2018 - 11:39
Anything else?
User
21/04/2018 - 11:39
No, not this time
User
21/04/2018 - 11:39
But this site seems awesome!
Excelchat Expert
21/04/2018 - 11:39
We are awesome, feel free to visit us anytime.
User
21/04/2018 - 11:39
Thank you very much, you have saved me 2 days of work on my masters thesis
Excelchat Expert
21/04/2018 - 11:39
Also please leave a good rating and feedback comment on our service.
Excelchat Expert
21/04/2018 - 11:40
Glad to be able to help.
User
21/04/2018 - 11:40
I will do, do i do that on the front page?
Excelchat Expert
21/04/2018 - 11:40
You will be asked to rate and for feedback once close this chat session.
Excelchat Expert
21/04/2018 - 11:40
I believe there is a end/close button on your side.
User
21/04/2018 - 11:40
Okay, back to work for me then
User
21/04/2018 - 11:40
Yes there is
Excelchat Expert
21/04/2018 - 11:40
Thanks and have a great day!
User
21/04/2018 - 11:41
Again, thank you, have a great day
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.
