Need a formula to lookup/match two values and return a third in Excel

December 28, 2009 9:21 AM

Need a formula to look for two values in another sheet, and return a third corresponding value. Example provided (google spreadsheet.)

I've searched for and found various answers on using VLOOKUP and other similar functions, but it might be easiest if I just post this example and ask for the best way to approach it.

Google Spreadsheet example (though I am actually using Excel 2003)

The first worksheet has a table for sales of various fake store locations. I made this up from scratch, but the entire example represents data that I have to copy from a web-based report interface that I can't automate or change the layout for.
Highlighted in yellow are cells for products on the second sheet that I need to pull in data for. The order on each sheet is variable from day to day, meaning that you can't count on the cells to always be in the same place. They are also named differently on each sheet.

I need to allocate the Candied Apples sales from the Seasonal sheet to the Apples sales for the Main Location for every day. I also need to do the Oranges and Banana examples for the Main location. I've done the first one manually. This is a short example, but I've got two years worth of data in my real worksheet.

posted by malcommc to work & money (5 answers total)

Have you looked into using a pivot tables to make a report on worksheet A's data ?

Store all your data on sheet A, update your pivot table once a day on sheet B (make sure that the source reference stays up to date).
posted by IAr at 9:44 AM on December 28, 2009

A quick solution I'd use for Excel 2003:

On the FruitSales worksheet, on one of the leftmost columns, add a Date column, say column J. On J2:Jxxx write a function that says '=if(A2~a date,A2,J1)' so that you've added a date column to each row.

Then add a third Code column, say K, which is just '=B2&C2&J2', a combination of the three bits that you need sorted. This is a code lookup which can be used to reference the sales data.

In the next spreadsheet, you basically need to create a similar structure: Columns for Date, Location, Fruit; each rotating through the available values.

The just use a VLOOKUP, INDEX-MATCH, or SUMIF function, using the Code that would be generated for each line. Excel 2007 has a SUMIFS function which would simplify this, but you'd still need a Date column in each sheet.
posted by FuManchu at 9:51 AM on December 28, 2009 [1 favorite]

Maybe helpful: you can reference cells on other sheets by using "SheetName!$C$R" where SheetName is the text of your sheet name (like "Sheet1" or "FruitSales") and C and R are the col and row, so, an example, the 2nd column, 3rd row of the FruitSales sheet is FruitSales!$B$3

If the cells are moving around on the source though you'll need to keep changing any references you've got. Gonna be hard to automate on unpredictable input you understand, always is, why are things moving around? Maybe there's ways to be cleverer about it
posted by qbxk at 9:59 AM on December 28, 2009

You can use SUMPRODUCT as a way to look up over multiple values as an alternative to the composite index that FuManchu suggests, see here:

Daily Dose of Excel » Blog Archive » VLOOKUP on Two Columns
http://www.dailydoseofexcel.com/archives/2009/04/21/vlookup-on-two-columns/
posted by Boobus Tuber at 10:29 AM on December 28, 2009

Thanks for all of your answers. They were all really helpful. Especially Boobus Tuber's link to that article on SUMPRODUCT. I'm still working through this, and will send an update if I am able to work it out.
posted by malcommc at 12:19 PM on December 29, 2009