# Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I need to formulate a cell where it will show different values. for example if d2=0 i need e2 to show N. But the range of d2 could be anywhere from 0-60
Solved by I. U. in 29 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 07/02/2018 - 07:08
Hello! Welcome to Got It Pro-Excel.
User 07/02/2018 - 07:08
Hello
Excelchat Expert 07/02/2018 - 07:08
Hi
Excelchat Expert 07/02/2018 - 07:08
'm so glad to assist you today
User 07/02/2018 - 07:08
Im very grateful for your help
Excelchat Expert 07/02/2018 - 07:09
Thank you :)
User 07/02/2018 - 07:09
shall I paste what i have onto the sheet?
Excelchat Expert 07/02/2018 - 07:09
Can you share some part of your file to support the issue posted above?
Excelchat Expert 07/02/2018 - 07:09
User 07/02/2018 - 07:10
ok
User 07/02/2018 - 07:10
so basically I am trying to assess my pupils scores on a test
Excelchat Expert 07/02/2018 - 07:11
okay
User 07/02/2018 - 07:11
So i calculate the raw score by adding paper one and two together
User 07/02/2018 - 07:11
then the result of that then relates to a scaled score
User 07/02/2018 - 07:11
so d2 currently equals 35
User 07/02/2018 - 07:12
I need the scaled score to represent
User 07/02/2018 - 07:12
the number next to 35 on this sheet
Excelchat Expert 07/02/2018 - 07:12
must be 99, right?
User 07/02/2018 - 07:12
Is this possible?
User 07/02/2018 - 07:13
yeah
Excelchat Expert 07/02/2018 - 07:13
Yes,
Excelchat Expert 07/02/2018 - 07:13
lease hold on, so I could create the formula for you
User 07/02/2018 - 07:13
thank you :)
Excelchat Expert 07/02/2018 - 07:13
My pleasure :)
User 07/02/2018 - 07:13
You are going to make me a very happy man if you do this!
Excelchat Expert 07/02/2018 - 07:14
Sure, thank you
User 07/02/2018 - 07:14
if tried this =IF(D2='2016 scaled score'!A2,'2016 scaled score'!B3)
Excelchat Expert 07/02/2018 - 07:15
Anyway, this is the formula for you
Excelchat Expert 07/02/2018 - 07:15
=vlookup(D2,Sheet2!\$A\$1:\$B\$62,2,false)
Excelchat Expert 07/02/2018 - 07:15
Excelchat Expert 07/02/2018 - 07:16
This method of using VLOOKUP function will give the best help to resolve the scenario like yours
Excelchat Expert 07/02/2018 - 07:17
Based on the formula, this means, we are looking the value of D2 at the table you've set that ranges from Sheet2!\$A\$1:\$B\$62
User 07/02/2018 - 07:18
I don't know how to apply that on to my workbook :(
Excelchat Expert 07/02/2018 - 07:18
Since the second column will be pulled out once the value is found, so let's put 2 as indicator at the formula
User 07/02/2018 - 07:18
ive typed =lookup(D2 then clicked the skeet
Excelchat Expert 07/02/2018 - 07:19
Simply follow the syntax of using the VLOOKUP formula, then based on our sample yoou can do the same way
User 07/02/2018 - 07:20
=vlookup(d2,'2016 scaled score'!A1:B62
Excelchat Expert 07/02/2018 - 07:20
=vlookup(D2,Sheet2!\$A\$1:\$B\$62,2,false)
Excelchat Expert 07/02/2018 - 07:21
Your formula is near it now.
User 07/02/2018 - 07:21
i think i have it
Excelchat Expert 07/02/2018 - 07:21
You must continue to put comma (,), then the number column like (2), then comma again, and then the word false
User 07/02/2018 - 07:21
but it has n/a for some
Excelchat Expert 07/02/2018 - 07:22
Yes, it will show n/a when the value is no longer found in the table
Excelchat Expert 07/02/2018 - 07:23
Have you got it?
User 07/02/2018 - 07:23
Its so strange
User 07/02/2018 - 07:23
as if i have a score it has done it
User 07/02/2018 - 07:24
but the 0's come up with n/a
Excelchat Expert 07/02/2018 - 07:24
By the way, incasethe value is not found or an error remarks like N/A, what comment you want to show
Excelchat Expert 07/02/2018 - 07:24
0 shows "N"
User 07/02/2018 - 07:25
it should do!
User 07/02/2018 - 07:25
but if i ad dnew values it just shows na
User 07/02/2018 - 07:25
thats what i have for one child
Excelchat Expert 07/02/2018 - 07:25
Oh, take a look at our sample now
User 07/02/2018 - 07:25
I would uplaod it but it has confidential details
Excelchat Expert 07/02/2018 - 07:26
I understand, and we value confidentiality
User 07/02/2018 - 07:26
Ahh!
User 07/02/2018 - 07:26
I think i know why
Excelchat Expert 07/02/2018 - 07:27
If D2 is blank then it will show N/A
User 07/02/2018 - 07:27
ah no
Excelchat Expert 07/02/2018 - 07:27
But if it shows specifically zero 0, then it shows "N" as set on your table
User 07/02/2018 - 07:27
thats what i'm getting
Excelchat Expert 07/02/2018 - 07:28
Which?
User 07/02/2018 - 07:28
it works on some but not on others
Excelchat Expert 07/02/2018 - 07:28
Why
Excelchat Expert 07/02/2018 - 07:29
Please don't forget to put a dollar (\$) sign on the cell ranges
Excelchat Expert 07/02/2018 - 07:29
As shown on our sample inorder to make the range static
Excelchat Expert 07/02/2018 - 07:30
Look on how the dollar (\$) sign being placed on this range Sheet2!\$A\$1:\$B\$62
User 07/02/2018 - 07:31
YAAAAAS
User 07/02/2018 - 07:31
we have lift off!
User 07/02/2018 - 07:31
Thank you so much
User 07/02/2018 - 07:31
Can I ask you one more thing
Excelchat Expert 07/02/2018 - 07:31
It will be having a big different if you remove the \$ sign too just like this Sheet2!A1:B62
Excelchat Expert 07/02/2018 - 07:31
Sure
User 07/02/2018 - 07:32
If my raw score = 100 or greater I would like it to turn Green
Excelchat Expert 07/02/2018 - 07:32
Any more concern regardig this issue of ?
User 07/02/2018 - 07:32
If it less I would like it to be red
User 07/02/2018 - 07:32
Nope the formula is perfect now
Excelchat Expert 07/02/2018 - 07:32
Oh yeah, that will be possible by using formatting conditional
Excelchat Expert 07/02/2018 - 07:34
f you want mine, you can make another post for this topic of color coding so we could spend much time or the whole session to resolve yours since this requires more time again
User 07/02/2018 - 07:34
How do I do that?
User 07/02/2018 - 07:34
I think I will understand it quicker as I have done it before
Excelchat Expert 07/02/2018 - 07:34
Simply make a post again just like you did here to have a session
User 07/02/2018 - 07:35
ok! so end the session?
Excelchat Expert 07/02/2018 - 07:36
When everything is fine with us on this topic, simply click "End Session" , then you can make another post again regarding the color coding of your data.
User 07/02/2018 - 07:36
Ive managed it!
Excelchat Expert 07/02/2018 - 07:36
Only if this works for you
User 07/02/2018 - 07:36
Thank you so much for your help
Excelchat Expert 07/02/2018 - 07:36
Great :)
User 07/02/2018 - 07:36
This is an amazing service
Excelchat Expert 07/02/2018 - 07:37
My pleasure
Excelchat Expert 07/02/2018 - 07:37
Thank you too for coming
User 07/02/2018 - 07:37
Lets hope I can transfer this to my other sheets haha

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.