Multiple VLOOKUP???

[Sparky]

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.

 

[Fluff]

How about

Book1
	A	B
1	1	ABC123
2	1
3	6
4	1
5
6
7
Orders

Cell Formulas
Range	Formula
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))),"")

 

[Sparky]

How about

Book1
	A	B
1	1	ABC123
2	1
3	6
4	1
5
6
7
Orders

Cell Formulas
Range	Formula
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?

Again, thanks for your help.

 

[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.
Have a look here for some more info https://exceljet.net/excel-functions/excel-aggregate-function

 

[Sparky]

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.
Have a look here for some more info https://exceljet.net/excel-functions/excel-aggregate-function

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

 

[Fluff]

You're welcome & thanks for the feedback