• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup based on two criteria in source and target lists

R

roschino

New Member
I have two sets of data that I wish to combine. The first is forecast project spend and the second is actual project spend. Column headings are Employee, Project code, and January to December in both.


I want to produce a hybrid of the two whereby I add the actual value next to the forecast value in each row. I.e:


Employee > Project code > January (Forecast) > January (Actual) >...December (Actual)


The Employee names and Project codes are not unique so I need to look up based on the Employee name AND project code in the forecast data to find the correct actual values based on a match of the Employee name AND project name.


Is there a formula that would allow me to do this? It feels like a vlookup but with 2 critera to look up against and to criteria in the range to match against. Any ideas?
 
Since you're going to need to do this "double lookup" in multiple columns, I recommend using a few helper columns, one in each of your databases. Use a formula of:

=EmployeeCol & ProjectCode

You now have a single string you can use. Then, in the hybrid sheet, the lookup formula can be:

=INDEX(JanForecast,MATCH(Employee & ProjectCode,Forecast_HelperCol,0))


W/o helper columns you could construct this using SUMPRODUCT, but that may SUMPRODUCT formulas can start to affect calc time.

=INDEX(JanForecast,SUMPRODUCT(MIN(--(ForecastEmpCol = Emp),--(ForecastProjectCol = Project),Row(ForecastEmpCol))))
 
Thanks for your help, Luke. I've managed to get the first solution to work using this:


=INDEX(Col_JanActuals,MATCH(Cell_Helper_hybrid, Col_Helper_Actuals,0))


What I forgot to mention in the original post is that I made a third sheet (called 'Hybrid') rather than placing the actual values onto the forecast sheet. The way I'm understanding the formula above in plain English is "give me the Actual Value for January from the row on which you find the value of the helper cell on the current row in the helper column on the actuals sheet".


I'm struggling however with the SUMPRODUCT option. Please could you explain it in plain English factoring in that I'd like to enter the formula on my third sheet?


Many thanks,

Ross
 
roschino,

Yes, that's a good breakdown of the first formula. The second formula works similarly...


Looking inside the MIN function, you have 3 arrays which are being multiplied against each other. The first two are boolean arrays, thus returning a 1 (true) or 0 (false). By multiplying the 3, I'm generating a new array that consist of all the row numbers (the 3rd array in function) where the ForecastEmpCol = Employee name being searched for AND the ForecastProjectCol = the Project code being search for. Ideally this should only give one row number, but there is a small possibility of having multiple records. The MIN function then gives us the first row that meets this criteria (Note, you could use MAX, it depends on how your data is setup). Now that we have a single row number, we use that in the INDEX function to say "Look at row x in the JanActuals column and give me that value."
 
Thanks again Luke. I understand what you're doing there now but having a few problems still interpreting and entering the formula correctly. First of all, should it actually be =INDEX(JanActual,... if I want to find the correct actual value?


Also, I'm really struggling with the MIN formaula. If you could answer these questions I think I'll then be ok with it:

>What do the hyphens mean?

>Where you have ForecastEmpCol, am I just supposed to put the column reference e.g. B:B ?

>And am I meant to put '= Emp' and '= Project' or cell/column references there?


Many thanks, Ross
 
With much glee, I found your post today and tried it out for myself. I'm still not sure exactly how it works, but I am laughing maniacally and feeling full of Excel power. But since you didn't have your questions from nine months ago answered, I thought I'd try even though I'm not entirely sure what I'm doing.


I think the hyphens are really minus signs. Why you need them both, I don't know. I seem to get along fine with just one each time. I don't even know why you need them at all, but it seems that you do. I'm going to have to sleep on that one.


The MIN part of it didn't work for me, so I took it out. I know that my data doesn't have duplicates, and if it did, I would be fine with an error.


I found this easier to use as an Excel table (versions 2007 and up). So looking at the example formula Luke M created, "JanForecast" would be the name of the whole table. "ForecastEmpCol" is the name of the column that has the list of employees. "Emp" is the employee you are trying to get results for; I used a cell reference. "ForecastProjectCol" is the name of the column that has the list of projects and for "Project" I also used a cell reference like I did for "Emp".


Also (maybe because I am using a table), I put the table name of "JanForecast" in the ROW function part in place of "ForecastEmpCol". I also needed to subtract one because my table has headers. Finally, I put a column reference into the INDEX function to return the value that's in the third column of table. So my formula looks like:


=INDEX(JanForecast,SUMPRODUCT(-(JanForecast[ForecastEmpCol]=A3),-(JanForecast[ForecastProjectCol]=B3),Row(JanForecast)-1),3)


with cell A3 containing my employee name and cell B3 containing my project.
 
Also, I was thinking that if you use Excel 2007 or higher and the result you want to return is an amount/number, then you could use the SUMIFS function instead, which is much easier to understand.
 
I think I figured out why the minus signs are there. They turn the results of TRUE and FALSE into numbers -1 or 0. Two minus signs just turns TRUE into a positive 1, while one minus sign is a negative 1. Seems to work either way. Since it's mulitplication, all that matters is that you're either using one minus sign both times or two minus signs both times.
 
For me, I better understood how this works by looking at the formula results in the Function Arguements dialog box of Excel. To do this, place the cursor after the first paranthesis that follows SUMPRODUCT and click on "Insert Function". Then you can see the results that are returning for each of the three arrays and how they are multiplying against each other.
 
You must log in or register to reply here.
Back
Top