VLOOKUP - using two variables

kiranx

New Member
Joined
Oct 19, 2006
Messages
4
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
ABCDEFGHI
1AccountMaturityDate30Oct0630Nov0631Dec0631Jan0728Feb0731Mar0730Apr07
210010Nov065000400030002000100000
310105Jan0710000750050002500000
410C13Mar077000625055004750400032502500
520D15Apr07100045000000
Sheet1
:(
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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

Legend
Joined
May 15, 2003
Messages
29,536
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

New Member
Joined
Oct 19, 2006
Messages
4
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
ABCD
1Accountmaturity30Sep0631Oct06
210130Oct061000800
310211Nov0620001500
410320Jan0750003500
5
6
7
810130Oct06800
910211Nov061000
1010320Jan07500
11
Sheet1
 

kiranx

New Member
Joined
Oct 19, 2006
Messages
4
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

New Member
Joined
Oct 19, 2006
Messages
4
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..
 

Forum statistics

Threads
1,136,269
Messages
5,674,743
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top