Look up cell in a vlook up then retrieve cell next to it

[jesusespinoza1]

	A	B	C	D	E	F	G
1	JOB	Operator 1
2	Current rate	$10.00
3	Next Rate
4		Max	Rate 1
5	Operator 1	12.00	9.00	9.75	10.50	12.00
6	Operator 2	15.00	10.00	12.00	14.00	15.00
7	Operator 3	13.00	8.50	10.25	12.00	13.00
8
9

<tbody>
</tbody>

I have a compensation spreadsheet that has current wages and the salary structure for the upcoming year. I need to create a formula that will look up the job title and give me the next rate of pay based on the current pay. Lets say EE #1 is a Operator 1 making $10.00 per hour now. I want the formula to look up the pay range for Operator #1 and locate the next pay rate above $10.00. If the current rate is above the max rate then return the max rate

 

[JoeMo]

Re: Look up cell in a vlook up then retrieve cell next to it-- Please help

Excel Workbook
	A	B	C	D	E	F
1	JOB	Operator 1
2	Current rate	$10.00
3	Next Rate	10.5
4		Max	Rate 1
5	Operator 1	12	9	9.75	10.5	12
6	Operator 2	15	10	12	14	15
7	Operator 3	13	8.5	10.25	12	13
Sheet9

 

[jesusespinoza1]

Re: Look up cell in a vlook up then retrieve cell next to it-- Please help

The formula kind of works.
Issues:

1. The formula works as written but I have to makesure the last MATCH references the correct row: for example as its writtenMATCH(B2, C5:F5,1)+1)). It works in the original example because Operator 1 wasthe first pay range. But in my actualspreadsheet there are 32 jobs and the job I am looking for is not always in thefirst row.
   
   
   
   I basically need to enter a formula into Column E that willlook at the Job Title and Current Pay Rate and look that up on the wagestructure (2nd pasted picture below) and tell me what is the next Rate that person is due. Once I enterthat formula I can then drag the formula through column E to all 200+ employees

A	B	C	D	E	F
1	EE Name	Job Title	Current Rate	Next Rate
2	John	Slitter Operator	$15.50
3	Michael	Slitter Operator	$16.50
4	Patty	Mechanic	$18.00
5	Mary	Janitor	$11.50
6	Sean	Mechanic	$19.00
7	Steven	Janitor	$10.00
8	Joe	Planner	$19.75

<tbody>
</tbody>

 

[Aladin Akyurek]

Re: Look up cell in a vlook up then retrieve cell next to it-- Please help

In B3 control+shift+enter, not just enter:

=IF(INDEX($B$5:$B$7,MATCH(B1,$A$5:$A$7,0))=B2,B2,MIN(IF($A$5:$A$7=B1,IF($C$5:$F$7>B2,$C$5:$F$7))))

 

[jesusespinoza1]

Return cell next to a particular cell (IF, Match, Index)

A	B	C	D	E	F
1	EE Name	Job Title	Current Rate	Next Rate
2	John	Slitter Operator	$15.50
3	Michael	Slitter Operator	$16.50
4	Patty	Mechanic	$18.00
5	Mary	Janitor	$11.50
6	Sean	Mechanic	$19.00
7	Steven	Janitor	$10.00
8	Joe	Planner	$19.75

<tbody>
</tbody>

I basically need to enter a formula into Column E that willlook at the Job Title and Current Pay Rate and look that up on the wagestructure and tell me what is the next Rate that person is due. Once I enterthat formula I can then drag the formula through column E to all 200+ employees

1 A

B​

C​

D​

E​

F​

E​

F g​

I​

J​

K​

L​

2​

Job Title​

Empty 1​

Range Max​

0​

0.5​

1​

1.5​

2​

3​

4​

5​

6​

3​

Utility

$22.90 ​

$18.47 ​

$19.36 ​

$20.24 ​

$21.13 ​

$22.02 ​

$22.90 ​

$22.90 ​

$22.90 ​

$22.90 ​

4​

Planner

$22.85 ​

$18.43 ​

$19.31 ​

$20.19 ​

$21.08 ​

$21.96 ​

$22.85 ​

$22.85 ​

$22.85 ​

$22.85 ​

5​

Plate Maker

$23.12 ​

$18.64 ​

$19.54 ​

$20.43 ​

$21.33 ​

$22.22 ​

$23.12 ​

$23.12 ​

$23.12 ​

$23.12 ​

6​

Slitter Operator

$17.00 ​

$12.00 ​

$13.00 ​

$14.00 ​

$15.00 ​

$16.50 ​

$17.00 ​

$17.00 ​

$17.00 ​

$17.00 ​

7​

Mechanic

$22.73 ​

$18.33 ​

$19.21 ​

$20.09 ​

$20.97 ​

$21.85 ​

$22.73 ​

$22.73 ​

$22.73 ​

$22.73 ​

8​

Machine Operator

$22.28 ​

$17.97 ​

$18.83 ​

$19.69 ​

$20.55 ​

$21.42 ​

$22.28 ​

$22.28 ​

$22.28 ​

$22.28 ​

9​

Janitor

$15.00 ​

$11.00 ​

$12.00 ​

$13.00 ​

$13.50 ​

$14.50 ​

$15.00 ​

$15.00 ​

$15.00 ​

$15.00 ​

10​

<tbody>
</tbody>

 

[Eric W]

Re: Return cell next to a particular cell (IF, Match, Index)

Try:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	1	EE Name	Job Title	Current Rate	Next Rate				Job Title		Empty 1	Range Max		0	0.5	1	1.5	2	3	4	5	6
2	2	John	Slitter Operator	$15.50	$16.50				Utility			$22.90	$0.00	$18.47	$19.36	$20.24	$21.13	$22.02	$22.90	$22.90	$22.90	$22.90
3	3	Michael	Slitter Operator	$16.50	$17.00				Planner			$22.85	$0.00	$18.43	$19.31	$20.19	$21.08	$21.96	$22.85	$22.85	$22.85	$22.85
4	4	Patty	Mechanic	$18.00	$18.33				Plate Maker			$23.12	$0.00	$18.64	$19.54	$20.43	$21.33	$22.22	$23.12	$23.12	$23.12	$23.12
5	5	Mary	Janitor	$11.50	$12.00				Slitter Operator			$17.00	$0.00	$12.00	$13.00	$14.00	$15.00	$16.50	$17.00	$17.00	$17.00	$17.00
6	6	Sean	Mechanic	$19.00	$19.21				Mechanic			$22.73	$0.00	$18.33	$19.21	$20.09	$20.97	$21.85	$22.73	$22.73	$22.73	$22.73
7	7	Steven	Janitor	$10.00	$11.00				Machine Operator			$22.28	$0.00	$17.97	$18.83	$19.69	$20.55	$21.42	$22.28	$22.28	$22.28	$22.28
8	8	Joe	Planner	$19.75	$20.19				Janitor			$15.00	$0.00	$11.00	$12.00	$13.00	$13.50	$14.50	$15.00	$15.00	$15.00	$15.00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E2 =LOOKUP(D2,INDEX($M$2:$U$8,MATCH(C2,$I$2:$I$8,0),0),INDEX($N$2:$V$8,MATCH(C2,$I$2:$I$8,0),0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

I added a column with zeros in M. This is to handle the cases where the current pay is less than the minimum, like rows 4 and 7. You can hide the column if you want.

 

[jesusespinoza1]

Re: Look up cell in a vlook up then retrieve cell next to it-- Please help

Thanks Aladin. The formula works.
However when I drag the formula down it doesn't work anymore. I have 200+ Employees and I need to enter that formula in a spreadsheet and drag it down.

 

[Aladin Akyurek]

Re: Look up cell in a vlook up then retrieve cell next to it-- Please help

Thanks Aladin. The formula works.
However when I drag the formula down it doesn't work anymore. I have 200+ Employees and I need to enter that formula in a spreadsheet and drag it down.

It's your layout as you specified in post #1 which does not allow "copying down", but it does to the right...

Book1
	A	B	C	D	E	F	G
1	JOB	Operator 1	Operator 2
2	Current rate	$10.00	$12.00
3	Next Rate	10.5	14
4		Max	Rate 1	Rate 2	Rate 3	Rate 4
5	Operator 1	12	9	9.75	10.5	12
6	Operator 2	15	10	12	14	15
7	Operator 3	13	8.5	10.25	12	13
Sheet1

In B3 control+shift+enter, not just enter, and copy across:

=IF(INDEX($B$5:$B$7,MATCH(B1,$A$5:$A$7,0))=B2,B2,MIN(IF($A$5:$A$7=B1,IF($C$5:$F$7>B2,$C$5:$F$7))))

 

[jesusespinoza1]

Re: Return cell next to a particular cell (IF, Match, Index)

Eric thanks so much. It works great.

Just one thing maybe you can help with. When the employees current rate is above the MAX Rate can it return the current rate. Is it possible to tweak formula to add this? This would make it work perfectly.

 

[Eric W]

Re: Return cell next to a particular cell (IF, Match, Index)

See if this works for you:

=IF(D2>=INDEX($V$2:$V$8,MATCH(C2,$I$2:$I$8,0)),D2,LOOKUP(D2,INDEX($M$2:$U$8,MATCH(C2,$I$2:$I$8,0),0),INDEX($N$2:$V$8,MATCH(C2,$I$2:$I$8,0),0)))