If and Vlookup Query

Lainyb · External Since: Nov 06, 2009 Posts: 3

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

--
Lainyb

Pete_UK · External Since: Apr 17, 2007 Posts: 380

Try this:

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

Hope this helps.

Pete

On Nov 6, 9:44 am, Lainyb <Lai....TakeThisOut@discussions.microsoft.com> wrote:
> 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
>
> --
> Lainyb

Ms-Exl-Learner · External Since: Sep 15, 2009 Posts: 25

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!

--------------------
(Ms-Exl-Learner)
--------------------

"Lainyb" wrote:

> 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
>
>
>
>
>
>
>
>
>
> --
> Lainyb

Brad · External Since: Mar 17, 2006 Posts: 154

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

--
Wag more, bark less

"Lainyb" wrote:

> 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
>
>
>
>
>
>
>
>
>
> --
> Lainyb

Lainyb · External Since: Nov 06, 2009 Posts: 3

Brad

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

"Brad" wrote:

> 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
>
> --
> Wag more, bark less
>
>
> "Lainyb" wrote:
>
> > 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
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > --
> > Lainyb

Lainyb · External Since: Nov 06, 2009 Posts: 3

Sorry

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

Thanks again

--
Lainyb

"Ms-Exl-Learner" wrote:

> 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!
>
> --------------------
> (Ms-Exl-Learner)
> --------------------
>
>
>
> "Lainyb" wrote:
>
> > 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
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > --
> > Lainyb

Brad · External Since: Mar 17, 2006 Posts: 154

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.

--
Wag more, bark less

"Lainyb" wrote:

> Brad
>
> Thanks very much - don't quite understand it but works a treat.
> --
> Lainyb
>
>
> "Brad" wrote:
>
> > 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
> >
> > --
> > Wag more, bark less
> >
> >
> > "Lainyb" wrote:
> >
> > > 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
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > --
> > > Lainyb