ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Triple Lookup Formula! (https://www.excelbanter.com/excel-programming/307683-triple-lookup-formula.html)

gr8guy

Triple Lookup Formula!
 
Hi,

I have a workbook with 2 sheets. sheet1 contains a 12 month tabular data (like a calendar) of Agents Promises taken/Kept productions in a callcenter:
it is something like this:

January
-------------------------------------------------------------------
Name | Taken | Kept | Kept% | Wrap | Quality|
-------------------------------------------------------------------
Don | | | | | |
-------------------------------------------------------------------
Liza | | | | | |
-------------------------------------------------------------------
Emily | | | | | |
-------------------------------------------------------------------
Sana | | | | | |
-------------------------------------------------------------------

February (.....this is on right side of January)
-------------------------------------------------------------------
Name | Taken | Kept | Kept% | Wrap | Quality|
-------------------------------------------------------------------
Don | | | | | |
-------------------------------------------------------------------
Liza | | | | | |
-------------------------------------------------------------------
Emily | | | | | |
-------------------------------------------------------------------
Sana | | | | | |
-------------------------------------------------------------------


On Sheet 2, there is a Filtered & Sorted Database (given Dynamic Name - "Database" using offset function) like this:

P.S: Kept% = Kept/Taken*100

Month | Name | Taken | Kept | Kept% | Wrap | Quality|
------------------------------------------------------------------------------
January | Don | 500 | 415 | 86 | 12% | 100 %|
January | Sana | 475 | 300 | 75% | 16 % | 98 % |
February| Sana |
.................. and so on........

My problem is to get the Following data from Sheet2 into Sheet1 in the individual cells as per the Month & Agent name & the header names viz. Taken/Kept/Kept%/Wrap/Quality. I want a formula or VBA code which will get this data based on the above which i stated. I tried Double Lookup using Offset & match from Chip Pearson's website but it works for 2 criteria not for 3.

Also, If for some reason in a particular month, if some agent was missing/left or changed to some other team or some new agent joined my team, the database will show the scores of the agent for that month, but how would i get the his name into sheet1 in the table as per the month.

If somebody requires me to mail a copy of the workbook, pls let me know.
I just need a formula for each individual cell in sheet1.

Any better ideas are most welcome!

Rgds,

Eijaz



Charles

Triple Lookup Formula!
 
Eijaz


If you want I'll take a look at your pgm. You can e-mail it to
.
Also include as much info as possible.


Charle

--
Message posted from
http://www.ExcelForum.com



All times are GMT +1. The time now is 08:37 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com