Dropdown list, Vlookup (table), Enter in number, and find cell referenced?

**Nated_g** · 04-22-2013, 02:46 PM

Hello all,
I'm trying to accomplish exactly that. I am fairly familiar with these formula's and data validation. But I can not seem to put these two together and make it work.

I have several tanks listed as the type they are and in column 1 "inches", column 2 "volume for each tank. I want to have a drop down list that selects which tank, and then I can type in the inches and it presents the volume.

I am not familiar with arrays. If this is something that needs to be in a array I would need to know so I can read up and learn how to create them.

Any help would be appreciated. And if need be I would be able to attach the spread sheet to this post.

Thanks,
Ryan

**mehmetcik** · 04-22-2013, 02:50 PM

Can You please post a sample?

**Lemice** · 04-22-2013, 02:53 PM

Hello,

What you are interested in might be in this thread
http://www.excelforum.com/excel-form...down-list.html
If not, like mehmetcik suggested, please upload a sample file, it will be easier for others to help you.

**Harribone** · 04-22-2013, 02:58 PM

I'm not 100% sure on your tank data layout from the description but I think you will need:

COL A - TYPE
COL B - INCHES
COL C - JOIN
COL D - VOLUME

Enter you data for A B and D. In Col C starting in C2 type =A2&B2 than drag as far as required. This creates a unique type&inches value.

I will assume this is on 'Sheet2' and on 'Sheet1' you have a drop down in A1 listing tank types. In B1 you enter a volume. In C1 type =VLOOKUP(A1&B1,'Sheet2'!A:D,4,0). This should return your volume accordingly.

(Note: I assume 2003 vlookup is the same, may need replace 0 with FALSE in the formula if not working. Don't use it so am guessing)

**Nated_g** · 04-22-2013, 03:26 PM

Okay lets see if this can clear up what I am asking. I have this list of tanks with inches and volumes.Capture1.JPG
And I am trying to put a drop down list on the name of tanks, and tie my vlookup formula to it.
Capture.JPG

**Nated_g** · 04-22-2013, 03:34 PM

Harribone,
Will try, thanks.

**Harribone** · 04-22-2013, 03:41 PM

I don't know where your drop down and volume is so to help me explain lets assume the drop down cell is DROP and the inches cell is INCH.

On the XXXXX sheet where you have each tank listed with corresponding data I suggest you perfrom a vlookup for each tank.
So in A3 on this sheet type =VLOOKUP(INCH,A5:B67,2,0) this will lookup the inches you type in and return the volume. repeat this for each tank, changing the formula accordingly.
If your drop down list of tank names isn't exactly the same as shown in row 4 of XXXXX then change these to match.

You now have the volumes for each tank which matches the inches. You just need to get the right volume from these which relates to the drop down list of tank names.
In the cell where you want the correct volume type =INDEX(XXXXX!3:3,MATCH(DROP,XXXXX!4:4,0)).
This will find the tank name in row 4 and return the volume for that tank in row 3 (which has been matched up to the inches value already).

**Nated_g** · 04-22-2013, 04:27 PM

This would work great, and I have tried it. However I was hoping to have several independent drop down list for several tanks on the next sheet. I could make it work this way for each using a combination of index and matching. I was just curious if there was a easier way. I was trying to make your first example work because that would solve the issue of multiple dropdown list. Guess there is not going to be a easy way about it.

**Harribone** · 04-22-2013, 06:30 PM

Not sure what the issue of multiple drop down boxes is exactly in your work, both ways should work for multiple look ups.

The attached file is an example of howto use the first method I suggested. I've put on one sheet for ease and used mock data for quickness.
Try it with a few lines of real data if this helps you understand how it works better.

Note I have also used ISERROR in L4 so tht this remains blank when there is nothing to look for/nothing found. I think this is how to avoid errors using 2003 but i'm not sure. If there are issues in 2003 let me know.