i have data contains 1000 Chq no. i.e, A1 to A1000 (double chq no's) and amounts in B1 to B1000

Now i need to pickup same chq no. to same amount ie.,

chq no. amount

12345 1000000
12345 3000000

i am using Vlookup formula but my vlookup is pickuping only first value in B1 how to pickup second value through using vlookup.

kindly advice.



I want help, but I didn't get your problem. If you could give more details that will be great


Are you looking for the 2nd occurence all the time or are you looking for something to list all occurences?


Assuming you're always looking for the 2nd occurence. You could do this (although I am sure there's a more elegant way)

If the chq number is in D1 and you put this into E1: "=MATCH(D1,$A$1:$A$6,0)"

(I've trimmed the list down to 6 rows for testing)

This will return the row number of the first matching chq number.

If you then put this formula in another cell: "=VLOOKUP(D1,OFFSET(A1,E1,0,6-E1,2),2,0)" That should find the next occrence.

(note the use of the number 6 here - in your example I think you had 1000 rows, there's probably a nicer solution using the depth of the range)

hope this helps

Hi There

Hi Vinod,

Can You attache the sample file with require comments,
you looking for ??? please elaborate it then i will do my best to help you out