# VLOOKUP - using two variables

#### kiranx

##### New Member
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.

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
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
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
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
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
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..

Replies
3
Views
114
Replies
4
Views
184
Replies
2
Views
158
Replies
1
Views
167
Replies
2
Views
131

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.

### Which adblocker are you using?

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

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