Excel - IF Function Problem - Expert Solution

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.

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.