# Excel: lookup and Match functions

Microsoft Microsoft office excel 2007 ac...
May 8, 2010 at 05:11:10
Specs: Windows XP
 Hi, I am Charles. I want to use spreadsheet; using the lookup and match functions to work out the new salary of some employees.The first table should have the names of the employees in column one, next the salary, next the new salary.The second table should have the names of the employees(in a scattered order as in table one) and their corresponding commission in % in column 2.PROBLEM: if you change the commission of an employee in table two, the new salary should update it self in table 1.in table one, if I decide to change the position of an employee, the corresponding salary and new salary should update themselves. (if john was in row 5 and james in row 1; if I type john in row one, everything about john should follow automatically). I have been learning spreadsheet, manipulatinf formulars but got stocked in LOOKUP functions.Any ideas? Thanks

See More: Excel: lookup and Match functions

#1
May 8, 2010 at 11:12:26
 FYI...You're not being ignored.Based on my initial read, this looks a little complicated, only because you want to change something in Table 2, have those changes cause a change in Table 1, yet you say that Table 1 might not be static.I'll have to play with this a little and will probably have a few questions.For now, it's back to my plumbing project.

Report •

#2
May 8, 2010 at 13:23:16
 OK...Try this:For my example, I am going to use three Tables:Your Table 1 goes in Sheet1!A1:C4. Your Table 2 goes in Sheet2!A1:B4An additional table, Table 3, goes in Sheet1!H1:J4The formulas used may not make any sense until you read through this entire post:Table 1:```In Sheet1!A1:C4 - A B C 1 Name Salary New Salary 2 Sue =VLOOKUP(\$A2,\$H\$2:\$J\$4,2,0) =VLOOKUP(\$A2,\$H\$2:\$J\$4,3,0) 3 John =VLOOKUP(\$A3,\$H\$2:\$J\$4,2,0) =VLOOKUP(\$A3,\$H\$2:\$J\$4,3,0) 4 Fred =VLOOKUP(\$A4,\$H\$2:\$J\$4,2,0) =VLOOKUP(\$A4,\$H\$2:\$J\$4,3,0) ```By using VLOOKUP for the Salary and New Salary, you can change the position of the name and the data will follow. That's what Table 3 is for. More on that later.Table 2:```In Sheet2!A1:B4 - A B 1 Name Commission 2 Fred 10.0% 3 John 5.0% 4 Sue 4.3% ```Now for the 3rd table. I put this in Sheet1!H1:J4, but you can put it anywhere you want as long as you adjust the formulas to match.The reason for Table 3 is to satisfy your desire to move items around in Table 1. If you hardcode the Salaries and the formulas for the New Salaries in Table 1, you won't be able to move names around. By "looking up" the names in another table (Table 3) the Salary data will follow the names.Please note that I don't know exactly what formula you are using to determine the New Salary, so I'm using one that is often used to increase a number by a certain percentage, in the form of this example:=200*(1 + 5%) = 210=H3*(1 + Sheet2!B3)where H3 contains 200 and Sheet2!B3 contains 5%Table 3:```In Sheet1!H1:J4 - H I J 1 Name Salary New Salary 2 Fred 100 =I2*(1+VLOOKUP(H2,Sheet2!\$A\$2:\$B\$4,2)) 3 John 200 =I3*(1+VLOOKUP(H3,Sheet2!\$A\$2:\$B\$4,2)) 4 Sue 150 =I4*(1+VLOOKUP(H4,Sheet2!\$A\$2:\$B\$4,2)) ```Here's how this works:Hardcode your names and Salaries in Table 3, H2:I4.In J2:J4, use the VLOOKUP formula I suggested (or whatever formula you plan to use to determine the New Salary) I used VLOOKUP to lookup the name in Table 2 and pull the Commission percentage into the formula.So Table 3 is actually where the New salary is calculated.Now, back in Table 1, the VLOOKUP's are doing nothing more than pulling the data from Table 3.Please note that you can eliminate Table 3 if you add columns to Table 2 that contain the original Salaries and the formulas to calculate the New ones. (You can hide these columns if you don't want to see them) The concept of pulling the data into Table 1 from another table still holds, which is the only way you are going to be able to move names around in Table 1.I hope that helps. Feel free to come on back if this doesn't make sense.

Report •

#3
May 31, 2010 at 08:29:16
 Thanks, very much

Report •

Related Solutions