VLOOKUP and Multi Lists

[dpatte601]

Subject: VLOOKUP and Multi Lists

need help with this dropdown multi list with vlookup.
B1 is the dropdown list (on sheet named Data) that holds 4 lists (ad,
el, mr, sz) this are lists that hold the codes for group names (TB =The
Beatles BD= Bob Dylan..so on) each list has different number of rows.
I have Colum A on the Main sheet linked to the Above drop down, so that
if AD is selected in B1 then in A5 the dropdown list is AD and if B1 is
MR then A5 drop down is changed to MR. this part works good. Now the
problem,

I want colum C to show the Group name depending on what colum A has in
it.(I have the List setup and can get it to work (sort of) I have 2 ways
to do this now. The first works but only if the formula is changed to
match the colum A selection. This takes 4 different formulas

1. =VLOOKUP(A5,adata!E2,5,FALSE)
2. =VLOOKUP(A5,elata!F2,5,FALSE)
3. =VLOOKUP(A5,mrata!G2,5,FALSE)
4. =VLOOKUP(A5,szata!H2,5,FALSE)

but it is to much work to put in different formulas if 1 would do the
trick.So I changed it to look like this:

1. =IF($B$1="ad",(VLOOKUP(A5,adata!E2,5,FALSE)),IF($B$1="el",VLOOKUP
(A5,elata!F2,5,FALSE)))

2. =IF($B$1="mr",(VLOOKUP(A5,mrata!G2,5,FALSE)),IF($B$1="sz",VLOOKUP
(A5,szata!H2,5,FALSE)))

now it takes 2 to do this the best I could do was have 3 lists in 1 of
the above formulas (I can always change the lists to be 3 insted of 4
so this is not the issue) Heres the problem:

If A6 is TB then C6 is The Beatles.
but if I change the List in B1 to say EL so I can select EP in A7 so
Elvis Prestly shows up in C7 : then the Text in C6 changes to false.

What I am looking for is what can I do to keep the last selection from
changing every time I add new selection in a cell.

 

[Miguel Zapico]

Hi,

I don't know if I have understood correctly, but I have faced similar
situations and this an approach that worked for me:
1. Name the ranges where the list values are, including all the rows and
columns that will be searched using VLOOKUP (can be the same as you have now,
TB, EL, SZ, MR)
2. In the cell $B$1, or wherever you are selecting the code, make the
dropdown with the names that you have created in point 1
3. Use the INDIRECT in the VLOOKUP formula, something like this:
=VLOOKUP(A5,INDIRECT($B$1),5,FALSE)
The formula will search the named range that is represented in that cell.

Hope this helps,
Miguel.

 

[dpatte601]

Here is the worksheet , Maybe you can understand what i want if you see it
at work
http://www.savefile.com/files/8153879

 

[Miguel Zapico]

I have looked at it, and the approach I proposed earlier wouldn't work here,
you are right.
My proposal for the note in B15 is changing the formulas in C15:C18, to
avoid checking for the value in B1, but directly for the ranges. Something
like:
C15:
=IF(ISNA(VLOOKUP(A15,week1ata!E1,5,FALSE)),VLOOKUP(A15,elata!F15,5,FALSE),VLOOKUP(A15,week1ata!E1,5,FALSE))
C16:
=IF(ISNA(VLOOKUP(A16,week1ata!E2,5,FALSE)),VLOOKUP(A16,elata!F16,5,FALSE),VLOOKUP(A16,week1ata!E2,5,FALSE))
C17:
=IF(ISNA(VLOOKUP(A17,mrata!G17,5,FALSE)),VLOOKUP(A17,szata!H12,5,FALSE),VLOOKUP(A17,mrata!G17,5,FALSE))
C18:
=IF(ISNA(VLOOKUP(A18,mrata!G18,5,FALSE)),VLOOKUP(A18,szata!H13,5,FALSE),VLOOKUP(A18,mrata!G18,5,FALSE))

The formulas in C15 and C16 look for data in the "ad" and "el" options, and
C17 and C18 look for "mr" and "sz". They are not directly related to B1, so
I don't know if this will be a valid solution for you.

Miguel.

 

[dpatte601]

=?Utf-8?B?TWlndWVsIFphcGljbw==?=

=IF(ISNA(VLOOKUP(A16,week1ata!E2,5,FALSE)),VLOOKUP(A16,elata!F16,5,
FALSE),VLOOKUP(A16,week1ata!E2,5,FALSE))

this may work (with some work) but still if I change A15 so it now lists MR
then the #N/A comes up I need it to change as I select different lists in
B1.
You see this is the mainpage of the data base that has other pages (not
listed) that hold the CD names and band info. such as Name ,CD number, Type
of Music. there are 4 pages 1 is AD (from A to D) then there is E to L , M
to R , and S to Z.
What I need is a formula that can always hold the last B1 selection in the
cells even if B1 changes.
this formula
=IF(ISNA(VLOOKUP(A16,week1ata!E2,5,FALSE)),VLOOKUP(A16,elata!
F16,5,FALSE),VLOOKUP(A16,week1ata!E2,5,FALSE))
needs to incorporate MR and SZ as well as AD and EL
Hope this help you understand what Im looking for.
thanks

 

[Miguel Zapico]

Thanks for the explanation of the codes, with that, maybe this formula will
do what you need
=IF(LEFT(A15,1)<"E",VLOOKUP(A15,week1ata!E1,5,FALSE),IF(LEFT(A15,1)<"M",VLOOKUP(A15,elata!F1,5,FALSE),IF(LEFT(A15,1)<"S",VLOOKUP(A15,mrata!G1,5,FALSE),VLOOKUP(A15,szata!H1,5,FALSE))))
It checks the first letter of A15 to pick the correct list to search, and it
covers the four possibilities. Is this it?

Miguel.

 

[dpatte601]

=?Utf-8?B?TWlndWVsIFphcGljbw==?=

=IF(LEFT(A15,1)<"E",VLOOKUP(A15,week1ata!E1,5,FALSE),IF(LEFT(A15,1)<"
M",VLOOKUP(A15,elata!F1,5,FALSE),IF(LEFT(A15,1)<"S",VLOOKUP(A15,mra
ta!G1,5,FALSE),VLOOKUP(A15,szata!H1,5,FALSE))))

this was perfect - your great it worked the way I wanted it to. Many thanks