Variable Lookup in a table

[markvdh]

Hi Guys,

I would appreciate it if someone could help me with some sort of lookup function. I have attached my spreadsheet in which I am trying to do the lookups.

What I am trying to do is:

On the MONTHS tab, I am trying to lookup information from the UNITS tab. I have put into the MONTHS tab some formula purely to illustrate what I need the answer to be. Essentially, the lookup should use the date from the MONTHS tab (which will always be month end) as the value for the lookup on the UNIT tab. The complication is for example, in September on the MONTH tab, the lookup should use 30 September, but there are three September dates on the UNIT tab.

Under the DEFERRED & LIABILITY columns, I need to lookup the highest date in the September range. However, in the INCOME and INTEREST columns, I need the lookup to find September and add all the income and all the interest for various September dates (I have just used the SUM formula for illustration, but this won't work when the dates are variable.

I hope my explanation is a bit clearer than mud! I have tried to solve the problem, but the standard VLOOKUP and INDEX MATCH don't seem to work.

Thank you.

[dflak]

There are two concepts I will throw at you:
1. Named Dynamic Range
2. Array formulas

Normally, I would use Excel Tables, but your version of Excel (2000) does not support them. So I am using named dynamic ranges instead. The reasons for using them are:
- They are dynamic so when you add data to the range, you don't have to change formulas.
- You can use them in a formula as a name . That is, L_Income as opposed to E15:E50

Here is information on named dynamic ranges: http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges.
Here is information on array formulas: http://www.utteraccess.com/wiki/Array_Formulas

The ultimate formula you want for deferred is in cell B11 =INDEX(L_Deferred,MATCH(MAX(IF(MONTH(L_Date)&YEAR(L_Date)=MONTH(D2)&YEAR(D2),L_Date,FALSE)),L_Date,0),1).

I built this formula in parts from helper cells (that I left in) above it.

Cell B7 gets the maximum date in the range L_Date: =MAX(IF(MONTH(L_Date)&YEAR(L_Date)=MONTH(D2)&YEAR(D2),L_Date,FALSE)) - what this formula says is look at the range L_Date month and year and compare it to the month and year for cell D2. If it doesn't match, don't consider it. This will get you the maximum date for a specific month and year, even if it isn't the last day of the month. I was not sure that you would always have a last day of the month in the data.

Cell B8 finds the row in the range L_Date where this value occurs: =MATCH(B7,L_Date,0)

Cell B9 gets the the value in range L_Deferred on that row: =INDEX(L_Deferred,B8,1)

To get the big formula in B11, keep substituting helper formulas into the formula in B9 until everything is defined in terms of cell D2.

The formula for Liability was computed in the same way.

To get Income and Interest, use SUMPRODUCT. SUMPORDUCT is described in the article on array formulas.

[kvsrinivasamurthy]

ARRAY formulas
In B5
=IFERROR(INDEX(UNIT!$D$15:$D$50,LARGE(IF((MONTH(UNIT!$C$15:$C$50)=MONTH(D2))*UNIT!$D$15:$D$50>0,ROW(UNIT!$C$15:$C$50),""),1)-ROW($C$15)+1),"")

In C5
=SUM((MONTH(UNIT!$C$15:$C$50)=MONTH(D2))*UNIT!$E$15:$E$50)

In D5
=IFERROR(INDEX(UNIT!$F$15:$F$50,LARGE(IF((MONTH(UNIT!$C$15:$C$50)=MONTH(D2))*UNIT!$D$15:$D$50>0,ROW(UNIT!$C$15:$C$50),""),1)-ROW($C$15)+1),"")

In E5
=SUM((MONTH(UNIT!$C$15:$C$50)=MONTH(D2))*UNIT!$G$15:$G$50)

Copy to other regions respectively.

ARRAY formulas should be confirmed with Ctrl+Shift+Enter Keys.

[markvdh]

Thanks dflak. I am going to work through your suggestions & report back.

[markvdh]

Thanks kvsrinivasamurthy. I will work through it and report back. On the face of it, it looks like it is just what I needed.

[markvdh]

HI kvsrinivasamurthy. Thank you again. I just made one change in adding in a year condition to the formula (for a multi year period). Appreciate the time that you spend on my behalf.

[markvdh]

Hi dflak. I went with the solution from kvsrinivasamurthy. Appreciate your time in helping me. Thank you again.