VLOOKUP - using two variables

[kiranx]

HI

Need help with using vlookup.

The below sheet has account numbers in column A, Maturity date in Column B and the rest is the closing balances for the respective months for each account.

On another sheet I need to be able to select the account number, maturity date and the respective month end balance. eg. Account 101 Matures in Nov'2006 and the balace is 4,000.

I have tried in vain with vlookup, but now think its impossible.

Any help will be much appreciated.

Thanks in advance.

TABLE Sample.

Book1
	A	B	C	D	E	F	G	H	I
1	Account	MaturityDate	30Oct06	30Nov06	31Dec06	31Jan07	28Feb07	31Mar07	30Apr07
2	100	10Nov06	5000	4000	3000	2000	1000	0	0
3	101	05Jan07	10000	7500	5000	2500	0	0	0
4	10C	13Mar07	7000	6250	5500	4750	4000	3250	2500
5	20D	15Apr07	1000	450	0	0	0	0	0
Sheet1

 

[acw]

Hi

For the Maturity date, you can use the VLOOKUP function.

=vlookup(accno,A2:B5,2,false)

For the Balance, try using the INDEX and MATCH functions. Something like

=index(C2:I5,match(accno, a2:a5,0),match(enddate,C1:I1,0))

This does assume that the Account number is unique.


Tony

 

[Smitty]

Welcome to the Board!

See if this does it:

=INDEX(C2:I5,MATCH(A8,A2:A5,0),MATCH(EOMONTH(B8,0),C1:I1,0))

Note I tested it on US dates.

Hope that helps,

Smitty

 

[kiranx]

Tony

That works great..

I also managed to find on the net this option..

=VLOOKUP(A8,$A$2:$I$4,MATCH(EOMONTH(B8,0),$C$1:$I$1,FALSE)+2,FALSE)

Is there a faster method then the above as my spreadsheet is almost 54,000 rows??

Thanks

vlookup_two_variables.xls
	A	B	C	D
1	Account	maturity	30Sep06	31Oct06
2	101	30Oct06	1000	800
3	102	11Nov06	2000	1500
4	103	20Jan07	5000	3500
5
6
7
8	101	30Oct06	800
9	102	11Nov06	1000
10	103	20Jan07	500
11
Sheet1

 

[kiranx]

Thanks the response pennysaver..

I see there are many ways that one can get the same answer in excel.. just got to open up one's mind..

regards, and much appreciated.

Kiranx

 

[kiranx]

Hello again...

Just wanted to know, if I wanted to compute the average balance after the maturity date, eg for account 101 on the first sample sheet being (3000+2000+1000)/3, what kind of formula would perform such a task, and we need to ensure that it does not include the zero's after the balance becomes nil..

Thanks again..