Thread: Multiple nesting lookup, index, match

I attached a sample spreadsheet that I am working on.

Simply put, the NBFI tab will be input only. I will add values for each month that total for the Quarter. On the report tab, I want to use the drop down boxes to pick a business (in this example A & B) and a Quarter End value, which I have done with validation boxes. I then want excel to find the corresponding info and populate the cells.

I am thinking that it will be an array formula using match and hlookup, but need some help putting it all together. I will have a total of 5 workbooks for different branches and each branch will have a different number of businesses.

Thanks in advance,

Jeff

With a slight change in sheet structure you could easily use a Pivot Table to generate flexible, dynamic reports.

See if the attached is feasible. Otherwise, your layout is quite problematic for a formula solution, but may not be out of the question.

I think, but there will only be 1 entry per business each month. The date doesn't matter, right? I can just use the last day of each month for each business.

The date doesn't matter - Excel knows which quarter dates fall into. So just enter them as needed.

Assuming you are using the revised structure I suggested . . .

Working from memory here as I don't have Excel 2007 on this PC . . .

Select any cell in the data table, then on the Ribbon Menu, click the Insert Table and choose Pivot Table.

Excel should automatically recognize the entire table of data and select it as the source for the PT. From here you should be able to build the Pivot Table by dragging the Business Name to the Page Field. Drag the date to the row field and all of the fields you want to sum to the data pane in the dialog.

After the Pivot Table is created, click one into the row field, then right-click and choose the "Group By" option, then choose quarters as the grouping option.

If you really want a formula solution...see the workbook I attached.

Basically, I put reference text to the right of your report fields and created formulas that use those fields to locate the values you want calculated.

Using your posted workbook, here's the set up for the Items Cashed field:
C4: ...set to: 3rd Qtr '09
C6: ...set to: A
F8: Total Cashed

This formula finds and reports that value:

Code:

C8: =VLOOKUP($C$4,INDEX(NBFI!$2:$2,MATCH(Reports!$C$6,NBFI!$2:$2,0)):NBFI!$Z$1000,
MATCH($F8,NBFI!$2:$2,0)-1,0)

In the attached workbook, the other fields are populated.

Is that something you can work with?

As I pointed out earlier a formula solution is possible and Ron Coderre's solution works for your current worksheet. However, assuming more businesses will be added, you now have the issue of maintaining the formula and adjusting for new ranges, etc. In addition, you have needless redundancy in the structure and should seek to simplify.

IMO, you are much better served by revising your sheet per my suggestions and using a pivot table - it more easily accommodates growing data and has a lot of flexibility in obtaining different views/analyses of the data .

If you do so, you could convert the raw data into a Table (seeing you are using Excel 2007 - see the Help on Tables) and use the Table as the source for the Pivot Table. This makes the source "dynamic" and the Pivot Table can be refreshed to "see" changes in data.


Edit: never mind the comment about Table. I just took another look at your profile and it doesn't show Excel 2007.

JASON38967,

Your thread does not comply with the Forum Rules. Do not post a question in a thread started by someone else. Please start your own thread.

Rule #2

Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

Please be sure to post in the appropriate forum as there are several to choose from. Looks like your question should go in the General or Functions Forum.