go to next reference

[Legacy 15162]

If I setup a vlookup statement, how can i create the formula to not return the same value for matching numbers....
for example:
I have 12 pmts of 12.00 posted on the same day. If I have another worksheet that has say, 14 pmts of 12.00 I do not want the reference number to only pull up the first pmt and post it 12 times. I want the formula to find the first pmt and post that reference number then look for the next matching reference number for that pmt.
Also, there may be times when there are 12 pmts of say 10.00 posted on the first wksheet, but only 5 pmts on the second worksheet. How can I have the formula return to the top of the matching reference number?

 

[Jaamie]

More info

Txksa,

I think it might help to give us a bit more info. Particularly about the "reference number". Ie, do all payments have the same reference number. Are there orher payments with other reference numbers? Where are you trying to get these payments placed?

Jim..

 

[PaddyD]

see also the 'abitrary lookup' example here:

http://www.cpearson.com/excel/lookups.htm#DoubleLookup

paddy

 

[Legacy 15162]

thanks paddyd! That actually is a big help....
=INDEX(B5:C19,SMALL(IF(B5:B19=B21,ROW(B5:B19)-ROW(B5)+1,ROW(B19)+1),C21),2)

now, how would i tie this formula together to return the first value, if there is a duplicate, then return the next...and so on.....

 

[PaddyD]

By setting up a grid that relates the items on the list to potential number of instances & refering to those values in the formula - see the example:

Book5
	A	B	C	D	E	F	G	H	I	J	K	L
3
4		ID	Num
5		a	1			1	2	3	4	5	6	7
6		b	2		a	1	5	8	10	14
7		c	3		b	2	6	9	11	15
8		d	4		c	3	7	12
9		a	5		d	4	13
10		b	6
11		c	7
12		a	8
13		b	9
14		a	10
15		b	11
16		c	12
17		d	13
18		a	14
19		b	15
Sheet1

The formula in f6 is:

{=IF(COUNTIF($B$5:$B$19,$E6)>=F$5,INDEX($B$5:$C$19,SMALL(IF($B$5:$B$19=$E6,ROW($B$5:$B$19)-ROW($B$5)+1,ROW($B$19)+1),F$5),2),"")}

...which can be copied across to the rest of the table. Equivalent results could also be achieved using an appropriately constructed pivot table...

paddy