If and Vlookup Query

[Lainyb]

I was wondering if anyone can help me create a look-up as I am tearing my
hair out trying to get it to work.

In main spreadsheet I have a cell N2 with 35.00 hrs or 37.00 hrs.

I want to use look at this cell and then use this cell to decide what lookup
should be used to achieve result.

I then use cell J2 (spinal column point) and look up defined lookup on
another spreadsheet to get the appropriate salary for 35hrs or 37hrs

My defined lookups are:

salary35

SCP Salary
1 11187
3 11534
5 11899
7 12246
9 12629
11 12976
13 13395 etc

and

salary37

SCP Salary
1 11827
3 12193
5 12579
7 12945
9 13351
11 13717
13 14161 etc

To summarise - I am trying to look up the number of hours a person works
(N2) and then use the spinal column point (J2) to lookup and produce in my
new cell the appropriate salary using the lookup tables - salary35 or
salary37.

Thanks

 

[Pete_UK]

Try this:

=VLOOKUP(J2,INDIRECT("Salary"&N2),2)

Hope this helps.

Pete

 

[Ms-Exl-Learner]

I don’t know whether this is what you want.

In sheet2 I have pasted the below data (Salary 35)

A Col B Col
SCP Salary
1 11187
3 11534
5 11899
7 12246
9 12629
11 12976
13 13395

In sheet3 (Salary 37)

A Col B Col
SCP Salary
1 11827
3 12193
5 12579
7 12945
9 13351
11 13717
13 14161

In Sheet1 J2 Cell paste this formula and mention the value of salary whether
it is 35 or 37 in N2

=IF($N2="","",IF($N2=37,Sheet2!$B2,Sheet3!$B2))

Copy the N2 cell formula and apply it to the remaining cells of N Column.

If the N2 value of salary is typed continuously in N2, N3, N4 etc., then the
above formula can be used.

If you want to keep the N2 cell value as stable then use this formula.

=IF($N$2="","",IF($N$2=37,Sheet2!$B3,Sheet3!$B3))

If this post helps, Click Yes!

 

[Brad]

Name the ranges
salary35 and salary37

=VLOOKUP(J2,INDIRECT("salary"&N2),2,FALSE) - if you need an exact match
or
=VLOOKUP(J2,INDIRECT("salary"&N2),2,FALSE) - if you need closest to without
going over

 

[Lainyb]

Brad

Thanks very much - don't quite understand it but works a treat.

 

[Lainyb]

Sorry

Can't get this to work but thanks anyway as Brad has helped me get what I
want.

Thanks again

 

[Brad]

Glad to help...

One small change
If you need closest to without going over
=VLOOKUP(J2,INDIRECT("salary"&N2),2)

If you do an search (F1) on "indirect" you will get alot of information - in
my opinon a very useful function.