Vlookup help
Posted by Chris A on April 04, 2001 1:35 AM
Hi
I have two tables, one contains a table of all employees in a company, with the headings, Name, Total Sales, Total Expenses. I also have a database contianing everyones sales and expenses made. I need to Total each individuals sales and expenses to go on to the employee worksheet. the problem i have had with V look up is that as there are more than one entry for each employee Vlookup reterns #REF
Posted by Aladin Akyurek on April 04, 2001 2:21 AM
Chris
Will assume that the first table contains distinct employees (no duplicates). You want to compute total sales and total expenses per employee on the basis of data that are in a second table. The second table may list a given employee zero or more times. If so, I suggest that you select, in the second table, all the cells holding employee names and name the range so selected EMPLOYEES via the Name Box. Again in this second table, select all sales values and name this range SALES via the Name Box. Finally, do the same for expenses and name it EXPENSES.
Now go to the first cell for sales in the first table and array-enter (that is, hit CONTROL+SHIFT+ENTER at the same time to enter)the following formula:
=SUM((EMPLOYEES=A1)*(SALES))
where A1 holds the name of the first employee.
Copy down this formula for other employees.
Iterate for expenses by replacing SALES with EXPENSES.
Aladin
