# Lists

#### xcalibr

##### New Member
Example:
If I create a drop down list of products I produce, and let's say that each product has a different production lead time which I want to display next to the product, how can I create a function that would display the lead time for a specific product chosen from the drop down list?

Thanks.

Xcalibr

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
you will have to create another sheet lets say "Time"... type all those products on A column and their respective lead on corresponding cell of col B.. (i am assuming that you have 1000 products)

then come back to your original sheet and click the first cell where you want the lead time to appear and type this code:

(I am assuming here that your drop down list are on A col)

=IF(A1<>"",INDEX(Time!\$A\$1:\$A\$1000,MATCH(LEFT(A1,4),LEFT(Time!\$B\$1:\$B\$1000,4),0)),"")

sorry here is the corrected code:

=IF(A1<>"",INDEX(Time!\$B\$1:\$B\$1000,MATCH(LEFT(A1,4),LEFT(Time!\$A\$1:\$A\$1000,4),0)),"")

List

Thank you for the information.

I have followed your instructions to the letter but the corresponding value that I'm getting instead of the time value is "#N/A". I created a sheet called "Time". In column "A" I have the product names. In column "B" I have times associated with the build lead times. The original sheet has the drop down list of products located in cell "A1" along with your formula in cell "B2".

And again, the value returned is "#N/A"

Maybe I missed something.

Regards,

Xcalibr

What type of dropdown list is it?

Can you post some sample data?

The list that I'm creating is done as follows:
Step 2: Validation
Step 3: Allow:
Step 4: "List" is then selected reflecting my data set.
This drop down list is located in column "A1" in the original sheet.

Then, I just created some test data within the "TIME SHEET" by adding letters "A-Z" in column "A" and numbers "1-26" in column "B".

I went back to the original sheet to test the model at which point is returned the "#N/A" value.

Well you can probably just use a VLOOKUP.

=VLOOKUP(A1, 'TIME SHEET'!A1:B26, 2,0)

Yes, that's great, thank you. How does your previous formula differ from the VLOOKUP function?

My previous formula?.

yes

Replies
1
Views
486
Replies
3
Views
124
Replies
2
Views
104
Replies
4
Views
160
Replies
3
Views
123

1,219,828
Messages
6,150,474
Members
450,966
Latest member
Yali

### 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.

### Which adblocker are you using?

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

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