# Thread: How to pull a cell value on separate worksheet based on multiple criteria

1. ## How to pull a cell value on separate worksheet based on multiple criteria

I have a workbook used to track campaign costs for multiple entities - i.e. total value of leads purchased per entity per lead provider. I am stuck trying to write a formula that will pull the correct lead rate from a different worksheet within the workbook. As lead rates change, a start date and end date are entered as well as the new lead rate. I tried this formula, =SUM(SUMIFS(CampaignAmount,Campaign,B\$2,CampaignCo mpany,\$A2,StartDate,"<="&\$C2)), but it is totaling all of the Campaign Amounts listed. I need it to return the amount based on most recent start date and after the most recent end date. Sample worksheets are attached.

Can anyone help me see what I'm doing wrong?

Many Thanks!

2. I believe you can accomplish what you want using the VLOOKUP function which accesses the rate data in Worksheet#2. Attached is a modified version of your spreadsheet.

To use VLOOKUP you need to create a lookup key in Worksheet#1 which concatenates the Entity, Campaign, and Date fields. See col. A of the attached worksheet.

The data in Worksheet#2 must be sorted by CampaignCompany, Campaign, StartDate. Then a key field needs to be added which concatenates the CampaignCompany, Campaign, and StartDate fields. See col. M.

The range of data cells in col. M and N has been named RatesTbl. The formula to lookup the rates in col. F is (for row 3), =VLOOKUP(A3,RatesTbl,2). It uses the Lookup Key in col. A, attempting to find a match in col. M, the first col. of the RatesTbl and return the corresponding rate from col. N. If it doesn't find an exact match, then it returns the next largest value that is less than the lookup value, which is what you want.

I believe this will always work as long as Worksheet#2 contains an entry for every combination of Entity and Campaign in Worksheet#1, along with a StartDate that is on or before the earliest Date in Worksheet#1 for each such combination.LeadCostsTracking-rev1.xlsx

3. ## The Following User Says Thank You to THill For This Useful Post:

ljalv66 (2014-04-02)

4. Ijalv66,

Is this what you are looking for?

=SUMIFS(CampaignAmount,Campaign,\$B3,CampaignCompan y,\$A3,StartDate,"<="&\$C3,StartDate,">"&MAX(EndDate ))

Sumifs.png

5. Don't know why the editor keeps placing spaces in the formula above (CampaignCompan y and (EndDate ). Please remove them if pasting the formula. Note: Referring to your sample worksheet, in your original formula, please change B\$2 to \$B2 and start formula on row 3 instead of 2 as noted in above formula. You do not need the outside SUM formula if I am understanding correctly what you want to do.

Maud

6. ## The Following User Says Thank You to Maudibe For This Useful Post:

ljalv66 (2014-04-02)

7. Thank you, Thank you! This was perfect! I appreciate you taking the time to look it over and offer a solution, just in time for my meeting

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•