VLOOKUP multiple results in a drop down list

Alright, here is my example of what I am trying to do. The 'Cost Per Pound' sheet contains the data (highlighted in green) that is being pulled from the query on the 'Current Inventory' sheet. I have bolded the fields that I want the drop down lists to work with (just the one column, Lot Numbers). Let me know if you have any problems viewing this or if I need to give you any more information. Thanks for the fast response, I appreciate it.

Alright, I did things how you explained and it worked for what you were thinking, but I am just not explaining what I need clearly enough.

H5 is pulling the variety latin name of A5 from a master workbook. Then G5, I5, and J5 are going into the sheet 'Current Inventory' and pulling the top most result of the VLOOKUP for Nemophila menziesii. In 'Current Inventory' there are three different results that could theoretically be pulled for that VLOOKUP on Nemophila menziesii. I want the lot number cell to be a dropdown menu of all the results for Nemophelia menziesii (the three that can be found in 'Current Inventory'). Then I am going to apply that to all of the lot number cells, so each cell has a drop down menu for matching latin names in the 'Current Inventory' sheet.

I can adjust the I and J columns to pull information based off of the lot number after the change is made. That shouldn't be difficult.

Let me know if I was unclear. And thanks for responding so quickly last night even though it was late. I left work and was unable to get back in front of a computer to try this out. Let me know what you think.

I wanted to write you back and tell you thank you. This will take me a bit of time to work through but I'm glad for it because I'll learn about how it functions as I'm doing it. I can tell just by going through it that it is set up exactly how I need it to be.

The time you and others take in here to help people is commendable. I'll mark this solved once I add everything in and make sure I'm able to get it to work all the way through. Thank you again.

I got it all put together and figured out. It works great and I can't thank you enough.

Hi Jerry, I know this an old post but I am hoping you can help.

I am trying to use the function vlookups but I cant seem it to work.
My example is very easy as attached..

All i want is if the customer code is found in the right column Q produce a drop down box in D3 with all the notes associated with it.

so for example for CA21 customer i would expect the notes in the drop down box to be
gggggggggggggggggggggggggggggggggg
aaaaaaaaaaaaaaaaaaaa
bbbbbbbbbbbbbbbbbb
ccccccccccccccccccccccccc

I have added your function to the attached excel file but I cant seem to get it to work.

Can you please provide any help !!!!!!!!!! that would be appreciated.

Thanks.

Sam.

Jerry, thanks very much for your quick reply and your response.

It is not quiet working for me when I transfer it across to my actual excel file.
i have now attached my actual file with SHEET1 having the notes and AgeDebDia Sheet being the master file that I want to bring across the notes from SHEET1

What is the array AccRNG ??
I cannot see it defined in the excel file you sent back...

=IF($A2="", Message, INDEX(Notes, MATCH($A2, AccRNG, 0)):INDEX(Notes, MATCH($A2&"z", AccRNG)))

Does the matching of the customer code have to be in Column A? Would it work if it was in Column B? ie $B2 is in my actual file.

One last thing if there are NO NOTES available for that customer can the default message be something like "NO NOTES FOUND FOR THIS CUSTOMER"

that would be better... thanks very much...

Sam.

Jerry - I think I have it working.... thanks for you all your help - i renamed the arrays and put it all one one worksheet for the validation to work.

The only think is how can I if there are NO NOTES available for that customer can the default message be something like "NO NOTES FOUND FOR THIS CUSTOMER"

Thanks again.

Sam.

Attached is the file - what i would like is instead of just a blank cell a comment saying "NO NOTES FOR THIS CUSTOMER"

Ideally i would like it to default to that..... if there are no notes associated.

Else maybe a comment saying "DROP DOWN FOR NOTES ON THIS CUSTOMER"

So it will be one or the other... either NO NOTES or DROP DOWN FOR NOTES depending if there are notes or not..

hope that makes sense... all else is working at the moment...

Thanks very much.

Jerry, after much headaches I have it working 100% !!

Thanks for all your help.......... much appreciated.

Cheers.

Originally Posted by JBeaucaire

Ok, a few tricks needed for this.

First I've installed a new function called VLOOKUPS. In short, it returns an array of answers the same way VLookup returns a single answer. It's used the same way a VLookup is, except there's only 3 parameters, not 4.

Next, we have to create a VLOOKUPS list for each of the items in your sheet H5:H25. I put these lists off to the right in columns O:AI. Each column is bringing back an array of matching Lot Numbers for each of the items in H5:H25.

Now, we need to create a Dynamic Named range for each of the lists in O:AI. I created the first two for you. If you open the Insert>Name>Define>ListH5 you will see that ListH5 is defined as:

=OFFSET('Cost Per Pound'!$O$1,0,0,MAX(IF(ISNA(MATCH("*",'Cost Per Pound'!$O:$O,-1)),1,MATCH("*",'Cost Per Pound'!$O:$O,-1)),IF(ISNA(MATCH(1E+306,'Cost Per Pound'!$O:$O)),1,MATCH(1E+306,'Cost Per Pound'!$O:$O))))

So, this formula makes a list of ONLY the cells in column O with values. Blanks are stripped away.

The formula for ListH6 is similar, it just changes the column references, like so:

=OFFSET('Cost Per Pound'!$P$1,0,0,MAX(IF(ISNA(MATCH("*",'Cost Per Pound'!$P:$P,-1)),1,MATCH("*",'Cost Per Pound'!$P:$P,-1)),IF(ISNA(MATCH(1E+306,'Cost Per Pound'!$P:$P)),1,MATCH(1E+306,'Cost Per Pound'!$P:$P))))

Last, in H5, I added the Data > Validation > List =ListH5.

The first two are done, you can do the remaining Dynamic Name Range definitions and Validation List links.

In I5, I also created the the INDEX(MATCH() formula to pull over the Common Name. Do the same thing for Inventory Available.

this is a huge help! i am curious, is there a quicker way to create the vlookups lists? i have about 4000 records that i need to go through. is there something that does that in your 'ArrayFunction.xls'?

Hello,

This is exactly what I was looking for, but unfortunatelly it won't work....
How do I make an array out of the listH5? I have tried putting the function as such:

HTML Code: 

and I get an error : Select at least 68 row(s).
This is probably due to the fact that the output expects 68 rows, but doesn't know where to put it (array)....

OK go it!

I needed to define my range as array, meaning selecting relevant range, clicking on formula bar and pressing CTRL+SHIFT+ENTER, and there my array appears!

Cheers!

Hoping this is still active!
I am trying to create a list look up. Example attached:
Select from Col A one of 8 selections (circuit (list)
I would like, in adjacent Col (B) another list or chart showing all items/audio type/sig in and sig out based on original circuit selection
Your help is greatly appreciated!!
John

Hello gransjo, and welcome to the forum. Unfortunately, you've inadvertently broken a forum rule. Please read the following. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

Sorry! Will start a new thread!