Vlookup formula for second or third occurence

bthumble

Board Regular
Joined
Dec 18, 2007
Messages
195
I have a financial statement that has the same account on different rows for different years. How can I pick up the second or third occurrence using a vlookup, index/match, etc? The layout of my data is below. As an example, I normally use =VLOOKUP(Q4,A:M,2,FALSE) with 2 representing the values for that specific period

Column A account number
Col B January Values
Col C February Values
Col D March Values
....
....
....
Col L November Values
Col M December values
Col N Year

Thanks for your assistance.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Mike Szczesny

Active Member
Joined
Feb 7, 2008
Messages
411
Assume your data range is A1:D4. Assume Acct#,Jan,Feb,Mar is in A1:D1. Assume a, b, c, d are acct# numbers in A2:A4. A2:D4 contain values for each month. Use the specif range than columns

A6: b
A7: =vlookup($A$6,$A$2:$D$4,2,false) 2 refers to Jan, 3 refers to Feb, etc. If you want just Feb values, use 3 in formula.

If you need to show more than month, you can use a modification of this formula. If you want to show three months, (Jan, Feb, March) at the same time, select A7:C7 for formula. Use =vlookup($A$6,$A$2:$D$4,{2,3,4},false). Use Cntrl-Shift-Enter. 2,3,4 refer to Jan,Feb,March in vlookup table.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,488
Messages
5,529,164
Members
409,852
Latest member
Perry123
Top