**Question description:**

*This user has given permission to use the problem statement for this blog.*

I need to match two column, if one number in a column number falls in a range within the 2nd column. The result is on track or off track.
Condition: (Column A: Age, ColumnB: Rank1-8)
Under Age 30, Rank 1-2 ontrack
Over Age 30, Rank 1-2 off track
Between Age 31-37, Rank 3-8 On Track
Between Age 38-45, Rank 5-8 On Track
Above 45, Rank below Rank 8 Off Track

Solved by F. Q. in 36 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
24/07/2018 - 08:35

Hi

Excelchat Expert
24/07/2018 - 08:35

How are you doing?

User
24/07/2018 - 08:35

Hi

User
24/07/2018 - 08:35

Can u help me with the formular

Excelchat Expert
24/07/2018 - 08:35

sure..

Excelchat Expert
24/07/2018 - 08:35

Do you have an excel to work with?

User
24/07/2018 - 08:36

the excel is private as I am doing analysis

Excelchat Expert
24/07/2018 - 08:36

ok

User
24/07/2018 - 08:36

I can type out some dummy column

Excelchat Expert
24/07/2018 - 08:36

yes i see it on the right give me 1 min

User
24/07/2018 - 08:37

How to produce the result automatically?

Excelchat Expert
24/07/2018 - 08:37

you are looking for the result column column c right

Excelchat Expert
24/07/2018 - 08:37

?

User
24/07/2018 - 08:38

Yes

User
24/07/2018 - 08:38

now I have manually compare age and see if they fall into the rank and determine if this person is on track or off track

User
24/07/2018 - 08:38

but I have many record

Excelchat Expert
24/07/2018 - 08:39

anything above age 30 and rank 1,2 is off track

Excelchat Expert
24/07/2018 - 08:39

anything below 30 and rank 1,2 is on track

Excelchat Expert
24/07/2018 - 08:39

right

Excelchat Expert
24/07/2018 - 08:39

how about below 30 rank 3-4

Excelchat Expert
24/07/2018 - 08:40

give me a general way you are comparing the rank and age to get the result

User
24/07/2018 - 08:40

I draw a table on the right

User
24/07/2018 - 08:40

a new table do you see it?

Excelchat Expert
24/07/2018 - 08:41

yes

User
24/07/2018 - 08:41

If below 30 and rank above is ontrack (although not happen in reality)

Excelchat Expert
24/07/2018 - 08:42

so 30 below and rank equal and above 2 then ontrack

Excelchat Expert
24/07/2018 - 08:43

ohh so anything below the rank of the age it is at then off track above is on track right

User
24/07/2018 - 08:43

so by 30 in Rank 3 is off track

Excelchat Expert
24/07/2018 - 08:45

31 and rank 3?

User
24/07/2018 - 08:45

on track

Excelchat Expert
24/07/2018 - 08:46

so 30 and 3 is also off track?

Excelchat Expert
24/07/2018 - 08:46

its on track right?

User
24/07/2018 - 08:46

Yes

User
24/07/2018 - 08:46

Yes on track

Excelchat Expert
24/07/2018 - 08:47

by your description by 30 you should at least reach rank 2 so anything above should be good is that right?

User
24/07/2018 - 08:47

Yes

Excelchat Expert
24/07/2018 - 08:47

ok give me 1 min to build the formula

User
24/07/2018 - 08:47

Thank you

Excelchat Expert
24/07/2018 - 08:53

just the 1st condition less than 30 any rank is on track right?

Excelchat Expert
24/07/2018 - 08:53

eg 25

User
24/07/2018 - 08:53

Yes

User
24/07/2018 - 08:58

Still there?

Excelchat Expert
24/07/2018 - 08:59

yes

Excelchat Expert
24/07/2018 - 09:00

using a nested if so will take some time to analyse the criterias

Excelchat Expert
24/07/2018 - 09:07

can you see the formula in column D

Excelchat Expert
24/07/2018 - 09:07

can you try to test it

User
24/07/2018 - 09:07

YEs

Excelchat Expert
24/07/2018 - 09:10

for your reference =IF($A2="","",IF($A2<=30,IF($B2>=1,"On Track","Off Track"),IF($A2<31,IF($B2>=2,"On Track","Off Track"),IF($A2<=38,IF($B2>2,"On Track","Off Track"),IF($A2<=45,IF($B2>5,"On Track","Off Track"),IF($B2>8,"On Track","Off Track"))))))

User
24/07/2018 - 09:10

I think it works thanks

Excelchat Expert
24/07/2018 - 09:10

welcome :)

Excelchat Expert
24/07/2018 - 09:11

please do click on end session and leave a feedback

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