Lookup Value in Range and return nearest number (lowest) beween sets of numbers within the range

[Swsimpson1]

[TABLE="width: 576"]
<tbody>[TR]
[TD="colspan: 2"]Challenge 1:
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]What you know:
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]The employee is in pay grade 3 and rate is $25.30
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 9"]Challenge: write a formula that looks up the grade, and then returns the value on that range in which $25.30 is nearest to (rounded down)
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Challenge 2:
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]Write a formula that returns the step number in which $25.30 is nearest to, in that pay grade
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"]
[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]
[TABLE="width: 512"]
<tbody>[TR]
[TD="class: xl394, width: 64, bgcolor: transparent"]Step
[/TD]
[TD="class: xl394, width: 64, bgcolor: transparent"]1
[/TD]
[TD="class: xl394, width: 64, bgcolor: transparent"]2
[/TD]
[TD="class: xl394, width: 64, bgcolor: transparent"]3
[/TD]
[TD="class: xl394, width: 64, bgcolor: transparent"]4
[/TD]
[TD="class: xl394, width: 64, bgcolor: transparent"]5
[/TD]
[TD="class: xl394, width: 64, bgcolor: transparent"]6
[/TD]
[TD="class: xl394, width: 64, bgcolor: transparent"]7
[/TD]
[/TR]
[TR]
[TD="class: xl394, bgcolor: transparent"]Grades
[/TD]
[TD="class: xl394, bgcolor: transparent"][/TD]
[TD="class: xl394, bgcolor: transparent"][/TD]
[TD="class: xl394, bgcolor: transparent"][/TD]
[TD="class: xl394, bgcolor: transparent"][/TD]
[TD="class: xl394, bgcolor: transparent"][/TD]
[TD="class: xl394, bgcolor: transparent"][/TD]
[TD="class: xl394, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl394, bgcolor: transparent"]1
[/TD]
[TD="class: xl395, bgcolor: transparent"]$20.25
[/TD]
[TD="class: xl395, bgcolor: transparent"]$20.50
[/TD]
[TD="class: xl395, bgcolor: transparent"]$21.00
[/TD]
[TD="class: xl395, bgcolor: transparent"]$21.33
[/TD]
[TD="class: xl395, bgcolor: transparent"]$21.71
[/TD]
[TD="class: xl395, bgcolor: transparent"]$22.08
[/TD]
[TD="class: xl395, bgcolor: transparent"]$22.46
[/TD]
[/TR]
[TR]
[TD="class: xl394, bgcolor: transparent"]2
[/TD]
[TD="class: xl395, bgcolor: transparent"]$22.00
[/TD]
[TD="class: xl395, bgcolor: transparent"]$23.25
[/TD]
[TD="class: xl395, bgcolor: transparent"]$24.00
[/TD]
[TD="class: xl395, bgcolor: transparent"]$25.08
[/TD]
[TD="class: xl395, bgcolor: transparent"]$26.08
[/TD]
[TD="class: xl395, bgcolor: transparent"]$27.08
[/TD]
[TD="class: xl395, bgcolor: transparent"]$28.08
[/TD]
[/TR]
[TR]
[TD="class: xl394, bgcolor: transparent"]3
[/TD]
[TD="class: xl395, bgcolor: transparent"]$23.33
[/TD]
[TD="class: xl395, bgcolor: transparent"]$24.00
[/TD]
[TD="class: xl395, bgcolor: transparent"]$25.00
[/TD]
[TD="class: xl395, bgcolor: transparent"]$25.78
[/TD]
[TD="class: xl395, bgcolor: transparent"]$26.62
[/TD]
[TD="class: xl395, bgcolor: transparent"]$27.45
[/TD]
[TD="class: xl395, bgcolor: transparent"]$28.29
[/TD]
[/TR]
[TR]
[TD="class: xl394, bgcolor: transparent"]4
[/TD]
[TD="class: xl395, bgcolor: transparent"]$25.00
[/TD]
[TD="class: xl395, bgcolor: transparent"]$26.10
[/TD]
[TD="class: xl395, bgcolor: transparent"]$27.20
[/TD]
[TD="class: xl395, bgcolor: transparent"]$28.30
[/TD]
[TD="class: xl395, bgcolor: transparent"]$29.40
[/TD]
[TD="class: xl395, bgcolor: transparent"]$30.50
[/TD]
[TD="class: xl395, bgcolor: transparent"]$31.60
[/TD]
[/TR]
[TR]
[TD="class: xl394, bgcolor: transparent"]5
[/TD]
[TD="class: xl395, bgcolor: transparent"]$27.00
[/TD]
[TD="class: xl395, bgcolor: transparent"]$28.40
[/TD]
[TD="class: xl395, bgcolor: transparent"]$29.80
[/TD]
[TD="class: xl395, bgcolor: transparent"]$31.20
[/TD]
[TD="class: xl395, bgcolor: transparent"]$32.60
[/TD]
[TD="class: xl395, bgcolor: transparent"]$34.00
[/TD]
[TD="class: xl395, bgcolor: transparent"]$35.40
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Saba Sabaratnam]

is this challenge to test our Excel skill?

Enter your data in range A1:H7

Enter grade in cell A9

Enter the following array formula in B9 by pressing Control + Shift + Enter to answer Challenge 1

=INDEX(INDEX($B$3:$H$7,MATCH(A9,A3:A7,0),0),MATCH(MIN(ABS(INDEX($B$3:$H$7,MATCH(A9,A3:A7,0),0)-25.3)),ABS(INDEX($B$3:$H$7,MATCH(A9,A3:A7,0),0)-25.3),0))

Enter the following array formula in B10 by pressing Control + Shift + Enter to answer Challenge 2


=INDEX(B1:H1,MATCH(MAX(IF((B3:H7=B9)*(A3:A7=A9), COLUMN(B3:H3),0)),COLUMN(B1:H1),0))

Let me know if I get pass marks


Kind regards

Saba

 

[Joe4]

is this challenge to test our Excel skill?

Looks like it could be homework or a class assignment (if so, I hope you get credit)!

The policy of the board is to not do people's homework/assignments for them, but rather maybe give some hints, point them in the right direction, or critique attempts that they have made.
But they have not posted anything that they have tried.

 

[Saba Sabaratnam]

Thank you Joe,

I thought about it and was not 100% sure about it. That is why I posted a response. Next time, I will ask questions if I am not sure before posting a response.

Kind regards

Saba

 

[Swsimpson1]

No it is definitely not homework or for a test other than me trying to figure out how to work this for a real life issue I am having at work, which i think is the purpose of the board, however ease let me know if i am off there. Apologies if I worded it like a kid, I am working on a very large project with over 20,000 rows and 62 columns and have heen at this for hours without end and am exhausted.

 

[Swsimpson1]

Thank you Saba. This is for a real life example here at work. I have my masters and am done with school and direct HR. My hope is this will help me with a mega database file for which i am in real life trying to actually make work through thisformula otherwise it will not work and there is no option B.

 

[Swsimpson1]

If you have this question for aomeone why not email them directly? By posting what you did on this thread it is going to effectively shut it down for anyone else who would have helped me.

 

[Joe4]

If you have this question for aomeone why not email them directly? By posting what you did on this thread it is going to effectively shut it down for anyone else who would have helped me.

Not true... you already have someone helping you. Have you tried their suggestions?

You just want to be careful in how you word your questions. We get many people who post their homework and assignments, and often post the questions verbatim (so it is quite evident what they are doing). In the future, I would just recommend not posting questions a manner that might resemble that. Many people here will ignore anything that looks like it might be an assignment (regardless if it has any replies or not).

 

[Saba Sabaratnam]

Hi Swsimpson,

We are happy to help you with your work related Excel question. Cheer up

Try my solution and let me know how you go. I tested it few times and it worked for me.

Kind regards

Saba

 

[Swsimpson1]

Saba,
Your formulas got us well on the way. Without your help, I would still be working on this and I need to keep the project moving without stopping. You helped me do that. Thank you!!!

Below is what the finalized formula looked like:

To lookup closest rate in the grade



=INDEX(INDEX(Structure!$D$3:$X$172,MATCH([@[Pay Grade]],Structure!$B$3:$B$172,0),0),SUM(IF((INDEX(Structure!$D$3:$X$172,MATCH([@[Pay Grade]],Structure!$B$3:$B$172,0),0)-[@[Pay Rate]])<0,1,0)))



To lookup the step the pay rate is on in the grade

=INDEX(Structure!$D$1:$X$1,1,MATCH([@[Closest Step on Current Pay Grade to Current Rate]], INDEX(Structure!$D$3:$X$172,MATCH([@[Pay Grade]],Structure!$B$3:$B$172,0),0), 0))