VLOOKUP and IF statement?

[DDT~123]

Hello All,

I have 4 worksheets within my workbook...
worksheet 1 = Roster
lists 2 different types of fruit. Apples & Oranges
worksheet 2 = Apple Report
has report data for analyzing apples
worksheet 3 = Orange Report
has report data for analyzing oranges
worksheet 4 = Report
this worksheet will need to perform a VLOOKUP for the words "Apple" or "Orange" from the Roster worksheet then pull data from worksheet 2 or 3 depending upon the matches.

What would be the formula to tell Excel to go to the correct Report worksheet?

 

[pplstuff]

I don't think you understand what vlookup does. Vlookup will only pull values from a single table. You may mean something like:

=if('roster'!a1="apple",vlookup(___applereporttable____),vlookup(___orangereporttable___)

Post data for an exact formula.

 

[Joe4]

this worksheet will need to perform a VLOOKUP for the words "Apple" or "Orange" from the Roster worksheet then pull data from worksheet 2 or 3 depending upon the matches.

You may be able to use the INDIRECT function in conjunction with VLOOKUP to get what you want. If you do a Google search, you can find examples of these.

 

[DDT~123]

Here's a little more detail of what I'm looking for...

Roster worksheet
Range E1:E10 contains the names of multiple employees
Range F1:F10 contains their job titles (Customer Service, Technical Service)

CSR worksheet
Cell A1 contains the team's Average Calls Taken for Customer Service.

TSR worksheet
Cell A1 contains the team's Average Calls Taken for Technical Service.

Dashboard worksheet
Cell B2 requests the team's stats for Total Number of Calls Taken based upon the type of calls they take.

The formula would need to look at range F1:F10 to determine whether it needs to pull the data from the CSR worksheet, or the TSR worksheet then copy the number into cell B2.

Overall, the Dashboard worksheet lists the individual agent's stats after performing VLOOKUP's from the Roster worksheet. We need the ability to compare them with other team members which accept the same types of calls.

 

[Joe4]

Did you take a look into using the INDIRECT formula in conjunction with VLOOKUP?
If you are unfamiliar with the INDIRECT function, check out Excel's built-in help files and/or Google search for examples.
Take a crack at it, and it you can get it to work, post back here with the formula you tried and we'll see if we can help you fix it up.

 

[pplstuff]

Well, if you have different numbers of CSRs vs TSRs, it will be more difficult to make a dynamic list (one which lengthens or shortens depending on the selection).

If you have roughly the same number of CSRs and TSRs, say 5 and 4 respectively, you could try this on your dashboard:

b3: =if($b$2="CSR",__FirstCSRNameInCSRSheet__,__FirstTSRNameInTSRSheet__)
^^drag this down five rows
c3: =if(b3="","",vlookup(b3,RosterWorksheet!$e$1:$f$10,2,0))
^^drag this down equal rows

This won't be dynamic meaning, if there's 10 CSR reps and 5 TSR reps, you'll have 5 blank rows (when viewing TSR).

To make it dynamic would probably be more complicated for me to put in text. But if you're interested enough, there are plenty of resources to make dynamic lists - try google.