I need to change arrays in a formula if value in a list changes

[pikab00m]

Hi everyone! I'm new to the forum, and if I posted in the wrong section, please let me know.
I have the following problem in Excel 2007:

I need to make a table in which i have a dropdown list. For every value that I pick (let's say X), a cell with the formula :
=VlookUp(Data_In_a_Cell;ArrayX;2;False)

So for value 1 of the pick, the cell will modify its formula to : =VlookUp(Data_In_a_Cell;Array1;2;False) ;
for value 2, the formula will be: =VlookUp(Data_In_a_Cell;Array2;2;False) and so on.
Assuming that i define Array1, Array2,.... ArrayX , how do I do that?

I tried using VBA, but it displays me an error, because the code wants to keep the proper value of the cell, but as soon as it replaces, Excel will do its job and input another value of the cell. The code won't work

Next I tried using Replace function, but I don't quite get it...
Please help me if you can.
For any questions, I'm available. See you!

Cross posted here: http://www.mrexcel.com/forum/showthr...a-list-changes
and here: http://www.ozgrid.com/forum/showthre...601#post619601

[Spencer101]

Without seeing an example workbook it would be hard to show you exactly how to do it, but you could use INDIRECT in the VLOOKUP to refer to the value selected by the drop down list validation.

If there are only a small number of possible values in the drop down perhaps you could use CHOOSE rather than INDIRECT but that would get messy very quickly if a large number of options are available.

[Marcol]

Cross posted here Mr Excel - I need to change arrays in a formula if value in a list changes

[pikab00m]

I thought it would be faster.
I also asked here: http://www.ozgrid.com/forum/showthre...601#post619601

---------- Post added at 06:12 AM ---------- Previous post was at 06:10 AM ----------

I thought it would be faster.
I also asked here: http://www.ozgrid.com/forum/showthre...601#post619601

[Palmetto]

As Spencer states, a sample workbook would clarify your needs.

Not sure I have the correct understanding, but in order to match the look up value with a specific array you would need a matrix that correlates the value to a specific array.

Use named ranges for all of the look up arrays, which I will assume are tables themselves.
Create a two column table correlating the lookup values to their asscoated array. Column-1 contains all look up values, column-2 contains names of named ranges.

Then you may nest VLOOKUPs and use the INDIRECT function to match the look up with correct array table.

=VLOOKUP(A1,INDIRECT(VLOOKUP(A1,Q1:R10,2,FALSE)),2,FALSE)

In this example, if the lookup value were "X", the nested VLOOKUP would search for "X" in the array correlation table contained in the range Q1:R10. If "X" matches "Array1" then INDIRECT interprets this string as a valid range and the first VLOOKUP searches for "X" in the associated look up table.

[Cutter]

Thanks Marcol

@pikab00m

Welcome to the forum. Please be aware that the forum rules require you to post links to any other forum where you have asked the same question. Please read the rule below and keep it in mind for your future threads. Thanks.

Rule 8. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

[pikab00m]

Hey again! Thank you for helping me and I'm sorry for cross-posting, I'll edit immediately.
As for the sheet in cause, I uploaded an example of what I'm trying to do.
For each value in the list case, I wish that the formula uses the corresponding array.
Check it out, I've put many more details in it.
Thank you everyone for help. I hope you can manage to give me a hand.
Peace!

[Spencer101]

Try the below formula in cell H5 on your example workbook.

=VLOOKUP(H3,INDIRECT("Array"&C3),2,TRUE)

Does that solve your problem?

[pikab00m]

Dude, you're a genius!!!!!!! Thank you so very much!!!
I tried a lot of things so far, but that solves it.
There's still one more thing to ask. If I want to reverse the process, to look for a value in the specific array corresponding to the value in the drop-down list and to return it's number, do i use match/ index and use the line above as a part of match?

What I mean is: in the example given, if i search for value: 500 in Array4 (the list value cell is 4), what line I use to return value 5?
Sorry if I'm not that coherent, I'm doing my best. Please help if you can, if not, I'll figure it out somehow... I guess :D.

[Spencer101]

Sorry, not sure I'm going to be much help on that second bit I'm afraid.

I've been searching my brain for a possible solution for you, but every way I know of doing "left lookups" wont work in this situation.

I'm sure it's possible, and if I manage to come up with an answer I will let you know.
In them mean time, fingers crossed that someone far more skilled with Excel than I will come along and give you the simple answer.

[Cutter]

fingers crossed that someone far more skilled with Excel than I will come along

They weren't available so I thought I'd take a shot.

Something like

=INDEX(INDIRECT("Array"&C3),MATCH(500,INDEX(INDIRECT("Array"&C3),0,2),0),1)

[Spencer101]

See... Someone far more skilled than I came along

[pikab00m]

Lovely solution, Cutter!
I couldn't help it but start defining some SubArrayS and solving like this: =MATCH(H16;INDIRECT("SubArray"&C3);0) [Where H16 is the data I'm looking for], but your solution saves my time.
It's really great thing what you people do around here. Thank you so very much for your help! It's been a pleasure to work with you.
I'll put the post as solved. Thanks a bunch!

[Cutter]

You're very welcome. Glad to have helped.