Facebook Linkedin Youtube Rss. All the secrets of Excel and the Business Intelligence. Primary Menu. Search for: Search. If you are using approximate-mode matching, your data must be sorted in ascending order by lookup value. Otherwise, you may get an incorrect results. Also be aware that sometimes text data may look sorted, even though it's not. Felienne Hermans has a great example of this problem here , from a cool analysis she did on actual Enron spreadsheets!
For example, perhaps you have order data in one table, and customer data in another and you want to bring some customer data into the order table for analysis:. One to pull in the customer name, and the other to pull in the customer state.
If you ever need to apply arbitrary categories to data records, you can easily do so with VLOOKUP, by using a table that acts as the "key" to assign categories. In the example below, we are using VLOOKUP to to calculate a group for each department using a small table named "key" that defines the grouping. In situations where you plan to retrieve information from more than one column in a table, or if you need to copy and paste VLOOKUP, you can save time and aggravation by using absolute references for the lookup value and table array.
This lets you copy the formula, and then change only the column index number to use the same lookup to get a value from a different column. For example, because the lookup value and table array are absolute, we can copy the formula across the columns, then come back and change the column index as needed. Absolute ranges are pretty ugly looking, so can make your VLOOKUP formulas a lot cleaner and easier to read by replacing absolute references with named ranges, which are automatically absolute.
For example, in the employee data example above, you can name the input cell "id" and then name the data in the table "data", you can write your formula as follows:. Not only is this formula easier to read, but it's also more portable, since named ranges are automatically absolute.
This is because hard-coded column index values don't change automatically when columns are inserted or deleted. In this example, the lookups for Rank and Sales were broken when a new column was inserted between Year and Rank. Year continues to work because it is on the left of the inserted column:. If you're getting data from consecutive columns, this trick lets you set up one VLOOKUP formula, then copy it across with no changes required.
For the first formula in cell C3, COLUMN by itself will return 3 because column C is third in the worksheet so we simply need to subtract one, and copy the formula across:. Taking the above tip one step further, you can use MATCH to look up the position of a column in a table and return a fully dynamic column index. An example would be looking up sales for a salesperson in a particular month, or looking up the price for a particular product from a particular supplier.
It may seem counterintuitive, but wildcards let you do an exact match based on a partial match :. If you like, you can adjust the VLOOKUP formula to use a built-in wildcard, like the example below, where we simply concatenate the value in H3 with an asterisk. This message was edited by Dave Hawley on You must log in or register to reply here.
Similar threads C. Chlwls Sep 22, Excel Questions. Replies 2 Views Sep 22, Chlwls Question Cell protection with a SQL view. Replies 0 Views Question Complex Vlookup "sumif" statement. Replies 11 Views Sep 24, Marcelo Branco. Solved Segmenting text contained in one cell according to the titles there contained. Noodles90 Sep 14, Excel Questions 2.
Replies 13 Views Was this reply helpful? Yes No. Sorry this didn't help. Thanks for your feedback. Choose where you want to search below Search Search the Community.
Thank in advance for any assistance. This thread is locked.
0コメント