Multiple VLOOKUP???

Sparky

Board Regular
Joined
Feb 18, 2002
Messages
210
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
How about


Book1
AB
11ABC123
21
36
41
5
6
7
Orders
Cell Formulas
RangeFormula
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))),"")
 
Upvote 0
How about


Book1
AB
11ABC123
21
36
41
5
6
7
Orders
Cell Formulas
RangeFormula
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.
 
Upvote 0
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top