vlookup from different tables dependent on a drop

Question

So I've been asked to make a 'skills matrix' using excel and seem to be struggling with the logic somewhat. I have created a dropdown list (x,y,z), each of the options refers to a different dataset. For example, if I were to select 'x' from the list I would like to return data, using vlookup, specifically from the relevant table. The intention is to create a form which will return people and skills based on the initial selection. I'm currently using =IF(ISERROR(SEARCH("x",$B$1)),"",VLOOKUP(P13:$P$16,$P$2:$S$16,1)) which works ok if I select 'x'. So basically I'm looking for something which will work like: If x then vlookup from table x, elif 'y' then vlookup from y, else z

If anyone can help me I'd be much obliged.

Answer 1

What I would do is the following, with a single search of a single value as I am not 100% sure I got what you want to achieve :

• Use the name manager to build a name for each of your data range to search through (X Y and Z) and give them a convenient name, for example X_Range, Y_Range, Z_Range
• Type in cell B1 the range to search (X, Y or Z)
• Type in cell B2 the value to look for

• Use the following formula to search cell B2 in the proper range you indicate in B1

  =VLOOKUP(B2;INDIRECT(B1 & "_Range");2;FALSE)

INDIRECT is the key. It will allow you to resolve the string that is build with the range name and the literal "_Range" as a range name...

Hope that helps