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.

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

I have been learning spreadsheet, manipulatinf
formulars but got stocked in LOOKUP

Any ideas? Thanks

See More: Excel: lookup and Match functions

Report •

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 •

May 8, 2010 at 13:23:16

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:B4
An additional table, Table 3, goes in Sheet1!H1:J4

The 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 •

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

Report •

Related Solutions

Ask Question