Which Lookup function to use

I have an example of what i am trying to solve at link below..
thanks!

Example...

I have tried different functions but need help!

Please re attach your example to this site. Your link seem to be a bit dodgy

This is a case of bad data layout. Use one table for data input, then you can easily create pivot tables with just a few clicks to show your results grouped and summed by whatever you want to highlight.

See attached. The sheet "RawData" has all the data in one table. The next sheets have various pivot tables to sum up the data by different aspects. Pivot tables are immensely powerful. The attached sample just scratches the surface of what's possible. No formulas, just a few clicks.

To get started with Pivot Tables, have a look at http://peltiertech.com/Excel/Pivots/pivotstart.htm

Jason.b75, can you expand the formula gracefully to be copied down to the other names? You are using an absolute reference to $B$2, which contains the name "Bill". If you add a few rows with dates between "Bob" and "Mary", how will your formula perform then?

This _is_ a classic case of bad data layout. Data is being gathered in a report format instead of a two-dimensional table. Basic rules of good spreadsheet design are not applied, hence the problem with reporting on the data. Sure, you can write a formula to fix up an individual case, but it's not portable and hard to expand. And it is not robust.

A pivot table based on a two-dimensional table is hard to beat.

Jason, that's a good workaround. But compare what you've just been through to create this formula with the few clicks required to create a pivot table. Also, the formula blanks out any errors. which may not be helpful.

This is not so much about whether or not a formula can be found to achieve the outcome. This formula is very specific and only works for the given dataset. If the dataset changes in six months time, the OP probably does not have a snowball's chance in hell adjusting it, since they don't understand how it works in the first place. Or, if another parameter is added to the mix, the formula can get really, really messy and unwieldy.

This is about being able to maintain a scenario in the real world of changing requirements.

With good practice spreadsheet design (i.e. raw data in one sheet, all reporting in other sheets), nobody needs to understand ANY formulas. People can just click and drag cells into position in the Pivot Table setup grid to make the required adjustments in the pivot table.

It's never a good idea to enter data in a layout that resembles a report, because that greatly restricts the reporting capabilities the data can offer.

I've never seen anyone run into trouble by separating data entry from data reporting.

I've seen plenty of cases where people run into trouble trying to report on data presented in a report format. Hence I will always recommend separating data entry from data reporting. The workbook in this question is a classic example.

Yes, you can construct formulas to create the desired results. Learning how these formulas work and being able to maintain them will be a much higher effort than learning how to use a few clicks to create a pivot table from a simple 2D table. I'm not disputing that YOU can create the formulas. I can, too. But it's still not good practice. Life can be a lot easier if proven, well-working techniques are applied to spreadsheet design.

http://www.excelforum.com/tips-and-t...sign-tips.html

If people prefer to go down the more complicated formula path, trying to report on badly laid out data, fine. I'm just trying to show a more efficient way to do it.

Mind you, even formula solutions work with less effort from a simple 2D table.