Data Search with Variable Column (3 solutions)
"I have a table with employee information and would like variable having a cell where the required information for me to return the information in the intersection between the employee who appears in the first column of the table and column variable. "
Although a possible solution and have in the post " Search with Tables and Column Indicator variable, and since I have received several similar queries, we will expand the article puts forward three different solutions with the following features: double VLOOKUP, VLOOKUP and INDIRECT and finally INDEX and MATCH . We start from the following example:
suggest the following input:
In cell C2 will select the employee you want the information and in cell C3 the specific information we need. We started creating with the tool Data Validation lists input. To do this we look at the cell C2 menu and go to Data / Validation and formulated as shown in the image:
We press OK and we will have our list down to C2. To do the same in C3 we give some intermediate step. The reason is that the content of the list of C3 will be the labels of the fields in the table. Such signs are found in horizontal and validation tool does not allow us the origin of the list has that provision. All you have to do is select the range B10: F10 and click Copy. Then we move into cell H3 and we Edit / Paste Special and select Transpose, and click OK. In the column I add the numbers shown in the image and then we will use:
Now we can put ourselves in C3 and proceed to generate the dropdown list ( Data / Validation ):
1. Double VLOOKUP solution
select the range B11: F20 and go to Name Box (left of formula bar). We click and write the name and press Data Enter. Then Do the same for the range H3: I7 and give the name, for example, ncol . This table is not simply the name of individual fields in the table and column number that is within that table (which we call Data ) . We are located in cell B5 and type the following formula:
= VLOOKUP (C2, data, VLOOKUP (C3, ncol, 2, FALSE), FALSE)
What we are doing is nesting a VLOOKUP in the argument column number of the function. As you can see in the image below, select the employee in C2 we see (empleado7) and C3 then select from the list the information you need:
2. VLOOKUP and INDIRECT solution
can get the same result nesting INDIRECT function in the argument column number of the VLOOKUP function. We selected the range H3: I7 menu and go to Insert / Name / Create . The window shown in the following picture and where we choose (and is selected by default) option left column:
We click OK . From now on the table names are all names of the labels in the data table. Pressing any of these names will lead to the column number they represent. And this is precisely what will help us with the INDIRECT function. We are located in cell D5 and type the following formula:
= VLOOKUP (C2, data, INDIRECT (C3), FALSE)
3. INDEX and MATCH solution
The INDEX function returns us to the intersection of a row and column of a matrix. Our array is the table that we called data. The number of row that we depend on our choice used in C2. To calculate how introduce row addresses MATCH function. The column number depends on the information requested in C3. We also use the MATCH function to get the column number. The formula that we write in F5 is:
= INDEX (data, MATCH (C2, B11: B20, 0), MATCH (C3, B10: F10, 0))
