# Thread: Multiple VLOOKUP??? Thanks: 0 Likes: 0

1. ## Multiple VLOOKUP???

Hi

I have consecutive numbers ranging from 1 to 1000 in column A (A2:A1001). Alphanumeric part numbers in column D (D2:D1001). On a separate sheet if I enter a number between 1 and 1000 in cell A1 for example I would like cell B1 to display the associated part number from sheet 1 column D. I know I could use VLOOKUP for this but I require the next number associated with the same part to be displayed in cell A2. Example: If the typed number 300 (sheet 2!A1) looked up part number ABC123 (sheet2!B1) and number 335 was also associated with the same part then sheet 2, cell A2 would display 335 and cell B2 ABC123, and so on.

Any help would be gratefully appreciated.  Reply With Quote

2. ## Re: Multiple VLOOKUP???

AB
11ABC123
21
36
41
5
6
7

Orders

Worksheet Formulas
CellFormula
B1=INDEX(Sheet1!D2:D12,MATCH(A1,Sheet1!A2:A12,0))
A2=IFERROR(INDEX(Sheet1!\$A\$2:\$A\$12,AGGREGATE(15,6,(ROW(Sheet1!\$A\$2:\$A\$12)-ROW(Sheet1!\$A\$2)+1)/(Sheet1!\$D\$2:\$D\$12=\$B\$1),ROWS(\$A\$2:\$A2))),"")  Reply With Quote

3. ## Re: Multiple VLOOKUP??? Originally Posted by Fluff AB
11ABC123
21
36
41
5
6
7

Orders

Worksheet Formulas
CellFormula
B1=INDEX(Sheet1!D2:D12,MATCH(A1,Sheet1!A2:A12,0))
A2=IFERROR(INDEX(Sheet1!\$A\$2:\$A\$12,AGGREGATE(15,6,(ROW(Sheet1!\$A\$2:\$A\$12)-ROW(Sheet1!\$A\$2)+1)/(Sheet1!\$D\$2:\$D\$12=\$B\$1),ROWS(\$A\$2:\$A2))),"")
Thank you Fluff. It does exactly what was required. Out of curiosity what does the AGGREGATE function do with 15,6 and also ROWS(\$A\$2:\$A9) at the end of the formula?  Reply With Quote

4. ## Re: Multiple VLOOKUP???

The 15,6 in the Aggregate function is the same as the Small function, but it ignores errors and the Rows unction will return 1 for the 1st row, 2 for the 2nd etc.  Reply With Quote

5. ## Re: Multiple VLOOKUP??? Originally Posted by Fluff The 15,6 in the Aggregate function is the same as the Small function, but it ignores errors and the Rows unction will return 1 for the 1st row, 2 for the 2nd etc.
Fluff

Thanks for the link. I've taken a look at the examples. With the 19 functions and 7 options it appears to be really useful.

Thank you  Reply With Quote

6. ## Re: Multiple VLOOKUP???

You're welcome & thanks for the feedback  Reply With Quote

## User Tag List

cell, column, number, part, sheet 