Thread: Vlookup to return the nth value. Thanks: 0 Likes: 0

1. Vlookup to return the nth value.

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.

2. Re: Vlookup to return the nth value.

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
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)

3. Re: Vlookup to return the nth value.

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

Code:
`=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.

4. Re: Vlookup to return the nth value.

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.

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•