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
Worker
3X512
3X412
3X513
2X513
1X013
1X613
1X513
0X313

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]What?=[/TD]
[TD="align: center"]X513[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]rngInvoice[/TD]
[TD="align: center"]Value[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Invoice[/TD]
[TD="align: center"]Received[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]X512[/TD]
[TD="align: center"] $ 13.20 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]X513[/TD]
[TD="align: center"] $ 33.00 [/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]X412[/TD]
[TD="align: center"] $ 16.50 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]X513[/TD]
[TD="align: center"] $ 23.10 [/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]X513[/TD]
[TD="align: center"] $ 19.80 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]X513[/TD]
[TD="align: center"] $ 19.80 [/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]X513[/TD]
[TD="align: center"] $ 23.10 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] [/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]X013[/TD]
[TD="align: center"] $ 26.40 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] [/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]X613[/TD]
[TD="align: center"] $ 29.70 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] [/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]X513[/TD]
[TD="align: center"] $ 33.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] [/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]X313[/TD]
[TD="align: center"] $ 36.30 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] [/TD]

</tbody>

VLOOKUP (4)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F4[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G4[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A4
[/TH]
[TD="align: left"]=IF(B4="","",COUNTIF($B4:$B$21,"*"&$G$1&"*")&B4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

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