Vlookup to return the nth value.

[Drachels2413]

This is my formula that returns the 1st value but I need to return the data from the 2nd and so on. My project is to pull data to populate invoices based on the invoice number.

=VLOOKUP(value(rngInvoice),Received_Process, 2,false)

 

[Toadstool]

I could do this with INDEX and AGGREGATE but if you want to use VLOOKUP then I'll need to add a worker column.

	A	B	C	D	E	F	G
1						What?=	X513
2
3	Worker	rngInvoice	Value			Invoice	Received
4	3X512	X512	$ 13.20			X513	$ 33.00
5	3X412	X412	$ 16.50			X513	$ 23.10
6	3X513	X513	$ 19.80			X513	$ 19.80
7	2X513	X513	$ 23.10
8	1X013	X013	$ 26.40
9	1X613	X613	$ 29.70
10	1X513	X513	$ 33.00
11	0X313	X313	$ 36.30

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

VLOOKUP (4)​

Worksheet Formulas Cell Formula F4 =IF(ROWS($F$3:F4)>(COUNTIF($B$4:$B$25,$G$1)+1),"",VLOOKUP(ROWS($F$3:F4)-1&$G$1,$A$4:$C$20,2,0)) G4 =IF(ROWS($F$3:F4)>(COUNTIF($B$4:$B$25,$G$1)+1),"",VLOOKUP(ROWS($F$3:F4)-1&$G$1,$A$4:$C$20,3,0)) A4 =IF(B4="","",COUNTIF($B4:$B$21,"*"&$G$1&"*")&B4) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[kweaver]

I believe you can do this with 1 ARRAY formula (CTRL+Shift+Enter):

=INDEX(Received_Process,SMALL(IF(InvList=FindValue,ROW(InvList)-ROW(INDEX(InvList,1,1))+1),2))

where the 2 is the second occurrence to be found in the InvList and return that value from the InvList. Change the 2 accordingly.

 

[kweaver]

Sorry, this: where the 2 is the second occurrence to be found in the InvList and return that value from the InvList. Change the 2 accordingly.

should have been where the 2 is the second occurrence to be found in the InvList and return that value from the Received_Process. Change the 2 accordingly.