[VivienW]

Hello again,

I'm tearing my hair out here and hope someone can help me (please)!

I need to use a VLOOKUP within a VLOOKUP. Below is the layout of my data.
Year data (G4:H8) is named YEAR and the other cell range (A3:M13) is named
SALARIES

A3
Salaries by Scale
G4 G5
Year 1 Year 2 Year 3 Year 4 Year Column
Scale 0% 5% 7% 9% 1 2
2 3
A £7,000.00 £7,350.00 £7,864.50 £8,572.31 3 4
C £7,200.00 £7,560.00 £8,089.20 £8,817.23 4 5
E £7,500.00 £7,875.00 £8,426.25 £9,184.61
G £7,700.00 £8,085.00 £8,650.95 £9,429.54
I £8,100.00 £8,505.00 £9,100.35 £9,919.38
K £9,000.00 £9,450.00 £10,111.50 £11,021.54
M £10,000.00 £10,500.00 £11,235.00 £12,246.15

Employee Scale Year Salary
Joe A 2 formula here to calculate the salary based on year of service and
scale
Sue C 3
Dave J 1
Stu K 4
John L 3

I'd really appreciate someones kind help.
--
Cheers, V

[Max]

Placed in say, D16, then copied down:
=INDEX($B$3:$E$9,MATCH(B16,$A$3:$A$9,0),VLOOKUP(C1 6,$G$4:$H$7,2,0)-1)

where
B16 = Scale, eg: A
C16 = Year, eg: 3

and
$A$3:$A$9 houses the scales: A, C, E etc
$G$4:$H$7 houses the 2 col YEAR table
$B$3:$E$9 houses the salary figs

Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"VivienW" wrote:
Hello again,

I'm tearing my hair out here and hope someone can help me (please)!

I need to use a VLOOKUP within a VLOOKUP. Below is the layout of my data.
Year data (G4:H8) is named YEAR and the other cell range (A3:M13) is named
SALARIES

A3
Salaries by Scale
G4 G5
Year 1 Year 2 Year 3 Year 4 Year Column
Scale 0% 5% 7% 9% 1 2
2 3
A £7,000.00 £7,350.00 £7,864.50 £8,572.31 3 4
C £7,200.00 £7,560.00 £8,089.20 £8,817.23 4 5
E £7,500.00 £7,875.00 £8,426.25 £9,184.61
G £7,700.00 £8,085.00 £8,650.95 £9,429.54
I £8,100.00 £8,505.00 £9,100.35 £9,919.38
K £9,000.00 £9,450.00 £10,111.50 £11,021.54
M £10,000.00 £10,500.00 £11,235.00 £12,246.15

Employee Scale Year Salary
Joe A 2 formula here to calculate the salary based on year of service and
scale
Sue C 3
Dave J 1
Stu K 4
John L 3

I'd really appreciate someones kind help.
--
Cheers, V

[Sheeloo]

See for a solution....

"VivienW" wrote:

Hello again,

I'm tearing my hair out here and hope someone can help me (please)!

I need to use a VLOOKUP within a VLOOKUP. Below is the layout of my data.
Year data (G4:H8) is named YEAR and the other cell range (A3:M13) is named
SALARIES

A3
Salaries by Scale
G4 G5
Year 1 Year 2 Year 3 Year 4 Year Column
Scale 0% 5% 7% 9% 1 2
2 3
A £7,000.00 £7,350.00 £7,864.50 £8,572.31 3 4
C £7,200.00 £7,560.00 £8,089.20 £8,817.23 4 5
E £7,500.00 £7,875.00 £8,426.25 £9,184.61
G £7,700.00 £8,085.00 £8,650.95 £9,429.54
I £8,100.00 £8,505.00 £9,100.35 £9,919.38
K £9,000.00 £9,450.00 £10,111.50 £11,021.54
M £10,000.00 £10,500.00 £11,235.00 £12,246.15

Employee Scale Year Salary
Joe A 2 formula here to calculate the salary based on year of service and
scale
Sue C 3
Dave J 1
Stu K 4
John L 3

I'd really appreciate someones kind help.
--
Cheers, V

[VivienW]

Thank you Max, you're a star! This does give the correct answer and thank
you for the explanation :)

I am really looking for a simpler solution for a particular reason. I need
to use VLOOKUPs but not INDEX and MATCH. Sorry to seem ungrateful - I am
delighted with your reply.

--
Cheers, V


"Max" wrote:

Placed in say, D16, then copied down:
=INDEX($B$3:$E$9,MATCH(B16,$A$3:$A$9,0),VLOOKUP(C1 6,$G$4:$H$7,2,0)-1)

where
B16 = Scale, eg: A
C16 = Year, eg: 3

and
$A$3:$A$9 houses the scales: A, C, E etc
$G$4:$H$7 houses the 2 col YEAR table
$B$3:$E$9 houses the salary figs

Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"VivienW" wrote:
Hello again,

I'm tearing my hair out here and hope someone can help me (please)!

I need to use a VLOOKUP within a VLOOKUP. Below is the layout of my data.
Year data (G4:H8) is named YEAR and the other cell range (A3:M13) is named
SALARIES

A3
Salaries by Scale
G4 G5
Year 1 Year 2 Year 3 Year 4 Year Column
Scale 0% 5% 7% 9% 1 2
2 3
A £7,000.00 £7,350.00 £7,864.50 £8,572.31 3 4
C £7,200.00 £7,560.00 £8,089.20 £8,817.23 4 5
E £7,500.00 £7,875.00 £8,426.25 £9,184.61
G £7,700.00 £8,085.00 £8,650.95 £9,429.54
I £8,100.00 £8,505.00 £9,100.35 £9,919.38
K £9,000.00 £9,450.00 £10,111.50 £11,021.54
M £10,000.00 £10,500.00 £11,235.00 £12,246.15

Employee Scale Year Salary
Joe A 2 formula here to calculate the salary based on year of service and
scale
Sue C 3
Dave J 1
Stu K 4
John L 3

I'd really appreciate someones kind help.
--
Cheers, V

[Sheeloo]

Ooops!

Here is the link
http://www.contextures.com/xlFunctio...tml#IndexMatch

"Sheeloo" wrote:

See for a solution....

"VivienW" wrote:

Hello again,

I'm tearing my hair out here and hope someone can help me (please)!

I need to use a VLOOKUP within a VLOOKUP. Below is the layout of my data.
Year data (G4:H8) is named YEAR and the other cell range (A3:M13) is named
SALARIES

A3
Salaries by Scale
G4 G5
Year 1 Year 2 Year 3 Year 4 Year Column
Scale 0% 5% 7% 9% 1 2
2 3
A £7,000.00 £7,350.00 £7,864.50 £8,572.31 3 4
C £7,200.00 £7,560.00 £8,089.20 £8,817.23 4 5
E £7,500.00 £7,875.00 £8,426.25 £9,184.61
G £7,700.00 £8,085.00 £8,650.95 £9,429.54
I £8,100.00 £8,505.00 £9,100.35 £9,919.38
K £9,000.00 £9,450.00 £10,111.50 £11,021.54
M £10,000.00 £10,500.00 £11,235.00 £12,246.15

Employee Scale Year Salary
Joe A 2 formula here to calculate the salary based on year of service and
scale
Sue C 3
Dave J 1
Stu K 4
John L 3

I'd really appreciate someones kind help.
--
Cheers, VO

[VivienW]

Again, thank you Sheeloo! Had a look and it helps to understand how INDEX
and MATCH work.
--
Cheers, V


"Sheeloo" wrote:

Ooops!

Here is the link
http://www.contextures.com/xlFunctio...tml#IndexMatch

"Sheeloo" wrote:

See for a solution....

"VivienW" wrote:

Hello again,

I'm tearing my hair out here and hope someone can help me (please)!

I need to use a VLOOKUP within a VLOOKUP. Below is the layout of my data.
Year data (G4:H8) is named YEAR and the other cell range (A3:M13) is named
SALARIES

A3
Salaries by Scale
G4 G5
Year 1 Year 2 Year 3 Year 4 Year Column
Scale 0% 5% 7% 9% 1 2
2 3
A £7,000.00 £7,350.00 £7,864.50 £8,572.31 3 4
C £7,200.00 £7,560.00 £8,089.20 £8,817.23 4 5
E £7,500.00 £7,875.00 £8,426.25 £9,184.61
G £7,700.00 £8,085.00 £8,650.95 £9,429.54
I £8,100.00 £8,505.00 £9,100.35 £9,919.38
K £9,000.00 £9,450.00 £10,111.50 £11,021.54
M £10,000.00 £10,500.00 £11,235.00 £12,246.15

Employee Scale Year Salary
Joe A 2 formula here to calculate the salary based on year of service and
scale
Sue C 3
Dave J 1
Stu K 4
John L 3

I'd really appreciate someones kind help.
--
Cheers, VO

[Sheeloo]

I am curious... why the insistence on VLOOKUP?

At least now you have mastered INDEX/MATCH for future use.

"VivienW" wrote:

Again, thank you Sheeloo! Had a look and it helps to understand how INDEX
and MATCH work.
--
Cheers, V

[Max]

.. does give the correct answer ...
.. I am delighted with your reply ..
... but, but you didn't click the YES button in that response ?

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"VivienW" wrote:
Thank you Max, you're a star! This does give the correct answer and thank
you for the explanation :)

I am really looking for a simpler solution for a particular reason. I need
to use VLOOKUPs but not INDEX and MATCH. Sorry to seem ungrateful - I am
delighted with your reply.

--
Cheers, V

[VivienW]

It's for a qualification set syllabus.
--
Cheers, V


"Sheeloo" wrote:

I am curious... why the insistence on VLOOKUP?

At least now you have mastered INDEX/MATCH for future use.

"VivienW" wrote:

Again, thank you Sheeloo! Had a look and it helps to understand how INDEX
and MATCH work.
--
Cheers, V