Need a formula to lookup/match two values and return a third in Excel
December 28, 2009 9:21 AM Subscribe
Need a formula to look for two values in another sheet, and return a third corresponding value. Example provided (google spreadsheet.)
posted by malcommc to Work & Money (5 answers total)
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.