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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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