Vlookup issue

[inq80]

Hi,

I need some advice and this forum and the people on it have been so helpful to me recently...

I've been trying to create a library app on excel and been faced with differing problems as I’ve gone along, each of which I’ve managed to the answer from this site. It’s getting quite complex so need some advice on whether I’ve done this the best way.

I’ve attached an image which would hopefully help explain the problems I’ve got.

It in the form of drop down lists which, dependent on your selection, brings up a separate list for you to choose from. I’ve changed the titles and options in the image but it gives you the idea; If you choose option 1, you then get to pick your colour, then the direction etc.

The practice use for this is going to involve a lot of repeat options hence the repeat choices in the image. Once you’ve made your choice, another formula will bring up a link to a word document. I am planning on differentiating between the choices with a number assigned to each choice; that’s why each one has a number against it.
If you choose option1, then red, then down, a separate cell will use a vlookup formula to tell you picked “112” (1-1-2). If you change to yellow it becomes “122” (1-2-2). Option 2, red, down would be “2518” (2-5-18) and so on.

The numbers were added as the strings would eventually be too long to work practically with. In the image I’ve just used directions in the list, but in reality, the options in the third and fourth drop down lists are going to be very long sentences, so I think it would be far easier to check for a number in a second column that a whole paragraph.
My problems begin with this vlookup. The drop down lists are easy to sort out using =INDIRECT, but the vlookup checks the entire column and returns the first match. So if I choose option1, blue, up, I’m hoping to get 1413 (1-4-13), but instead the vlookup is return the first match it finds in the column for “up” and returning just “1” so I’m getting “141” (1-4-1) instead. And when I choose option 2, blue up, Im trying to get “2829” (2-8-29), but instead I’m getting “241” (2-4-1); it’s return the wrong option from both the second and third drop down lists.

There’s no way I can change the names as it’s important to the whole point of creating it in the first place so is there a way of being able to tell it to return the “up” from the yellow list, instead of from the first “up” it finds which is in the wrong list?

The set up current has the drop down lists in cells D4, D6, D8 & D10 and the code used is:

=IF(COUNTBLANK(D4:D10)=4,"",(IF(D4<>"",VLOOKUP(D4,Lists!A3:B10,2,FALSE),"")&IF(D6<>"",VLOOKUP(D6,Lists!C2:D45,2,FALSE),"")&IF(D8<>"",VLOOKUP(D8,Lists!E2:F298,2,FALSE),"")&IF(D10<>"",VLOOKUP(D10,Lists!G2:H85,2,FALSE),""))*1)

The “IF(COUNTBLANK” code was added to make the box blank if nothing was selected and worked fine in a practice sheet I created when only 3 drop down lists were created, but now it’s got four its stopped and returns #VALUE?

The *1 at the end was to get it to recognize the value returned as a number which it wasn’t doing at first.

Any advice would be appreciated…

[inq80]

bump: anyone able to make a suggestion?

[oeldere]

88 viewers, no reply.

that indicates that the question is not clear.

Forummembers can't work in a JPG.

You also get better help if you add an small excel file, without confidential information, on the forum.

Please also add the desired (expected) result.

[inq80]

Thanks oeldere, makes sense -not sure why i just didn't do that in the first place...

I've attached an example of what i mean. You will see in the return box the values for the selected item. For book 1, chapter 1, paragraph 1 you get "1-1-1" as expected. If you select book 2 however and chapter 1, paragraph 1, I would need it to find "2-4-10", but it's returning "2-1-1". It's only finding the first reference to the search text in the column rather than the text in the specified list.

Is there a smarter way of doing this than I've set up?

[oeldere]

I re-aranged your data.

How about this solution?

Please reply.

[inq80]

that looks great! just going through it now to see what you've done...

ah right - selecting book 1, chapter 3 doesn't bring any options for the third drop down...

sorted - the lists weren't defined. That's very impressive but might take me a while to get my head round it...

Thanks oeldere.

The sample I added has three drop down lists and ultimately the one i'm creating with have four. I assume to add these i would do the same for the four option by creating a grid with all scenarios cross referenced with the appropriate number and use the index and match function to find it within the grid (array)?

Edit:

Actually i do have a question if that's okay? Is there any way of being able to jig it to only show "book 1" & "book 2" in the options for the first drop down? At the moment book 1 turns up 3 times because of the way it's defined as a list...

ignore me - sorted that by altering the definition of the list...

[oeldere]

function to find it within the grid (array)?

I suppose so, but I can tell you definitly, after I have seen your amended file (with those options).
It depends on the fact if the fourth one match all earlier criteria. (book 1, chapter 1 : 3).

At the moment book 1 turns up 3 times because of the way it's defined as a list...

Use a second sheet, and add there all options.
With a defined name you can use all options.
In the drop down box you need to refer to the defined names.

[inq80]

Hi,

I've had a play and managed to add a working fourth drop down list. Just two things - when you select the fourth drop down option, it brings them all up in the box - even though some return no value. For example, book_1, Chapter_1, Paragraph_1 should only allow RED or GREEN, but it gives all the colours as an option. Is there any way to fix this?

Also when nothing is selected H13-H16 return #N/A. Is there anyway to change this to be blank?

Thanks