This tells the VLOOKUUP formula not to select a single column, but to select all the ones listed and insert the value in the appropriate column. These curly brackets are entered manually. Then when we entered it, we used Ctrl+Shift+Enter rather than just Enter.Ģ) Second, notice that we did not have to "lock" either the lookup value or table array, since we are not copying the formula, but we could have – it would work either way.ģ) Third, instead of a single value or formula like the COLUMN function we used above, the column number is an array constant. When we started the formula, we actually selected all the cells that the formula would be in, from G21:K21. There are a few unique things with this formula:ġ) First, it's an array formula, notice the curly brackets around the entire formula. For the formula in the first cell, it will return the number 2 for the second column of the data range, and as we copy it to the right, that will index to 3, 4, 5, and 6 for the other columns.įor the second method, we are going to use the following formula: The only unique thing with formula is the col_num COLUMN(B1). I want to be able to select a fruit and have the values from the range above populate the cells to the right:įor the first method, we are going to use the following formula: Here we have a data range with various types of fruit and the values of each in 5 different columns:Īnd below that we have two different areas with Data Validation drop down lists with each fruit type. If you get a preview, look for the download arrow in the upper right hand corner.
You can download the file here and follow along.
One option will utilize an array formula using an array constant. In this tutorial we are going to look at two ways of using VLOOKUP to pull data from a range of multiple columns at one time.
0 kommentar(er)
