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

Yes please

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

As shown at your E2

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