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

Swsimpson1

New Member
Joined
Jun 12, 2018
Messages
5
Challenge 1:
What you know:
The employee is in pay grade 3 and rate is $25.30
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)
Challenge 2:
Write a formula that returns the step number in which $25.30 is nearest to, in that pay grade




Step
1
2
3
4
5
6
7
Grades
1
$20.25
$20.50
$21.00
$21.33
$21.71
$22.08
$22.46
2
$22.00
$23.25
$24.00
$25.08
$26.08
$27.08
$28.08
3
$23.33
$24.00
$25.00
$25.78
$26.62
$27.45
$28.29
4
$25.00
$26.10
$27.20
$28.30
$29.40
$30.50
$31.60
5
$27.00
$28.40
$29.80
$31.20
$32.60
$34.00
$35.40

<tbody>
</tbody>

<tbody>
</tbody>

<tbody>
</tbody>
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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).
 
Last edited:
Upvote 0
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
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top