|
If and Vlookup Query
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 a 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 |
If and Vlookup Query
Try this:
=VLOOKUP(J2,INDIRECT("Salary"&N2),2) Hope this helps. Pete On Nov 6, 9:44*am, 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 a 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 |
If and Vlookup Query
I dont 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 a 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 |
If and Vlookup Query
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 a 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 |
If and Vlookup Query
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 a 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 |
If and Vlookup Query
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 dont 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 a 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 |
If and Vlookup Query
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 a 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 |
| All times are GMT +1. The time now is 10:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com