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. They give you an easy way to create a "lazy match", but they also make it easy to find the wrong match.
In one way, this is useful because it tell you definitively that there is no match in the lookup table.
To trap this error and display a "not found" message instead of the error, you can simply wrap the orignal formula inside of IFERROR and set the result you want:. Here is the formula:. If you are simply retrieving numbers as text from a column in a table, it doesn't matter.
In the following example, the ids for the planet table are numbers entered as text , which causes VLOOKUP to return an error since the lookup value is the number To solve this problem, you need to make sure the lookup value and the first column of the table are both the same data type either both numbers or both text.
One way to do this is to convert the values in the lookup column to numbers. The formula in H6 is:. In the example below, Column B is a helper column that concatenates first and last names together with this formula:.
The message can be customized as desired. To return nothing i. The Excel LOOKUP function performs an approximate match lookup in a one-column or one-row range, and returns the corresponding value from another one-column or one-row range. Skip to main content. Lookup a value in a table by matching on the first column. Return value. Excel Usage notes.
Group arbitrary text values. This formula uses the value in cell E5 for a lookup value, the named range "key" H5:I9 for the lookup table, 2 to indicate "2nd column", and 0 as the last argument indicate exact match.
Translate letters to numbers. At the core, this formula uses an array operation to generate an array of letters from the input text, translates each letter individually to a number, then joins all numbers together again and returns the output as a Simple currency conversion. For example, when you protect a sheet or workbook, all of the cells will be locked, but you can also lock cells individually by right-clicking and selecting "Format Cells.
But not everyone is a fan of Excel, so if you need to convert Excel spreadsheets to Google Sheets , we have a guide for that, as well as a guide on how to open Google Sheets in Excel. For business users, we also have 10 Excel business tips that can help you keep your job, including guides on how to remove duplicate data, recover lost Excel files, use pivot tables to summarize data, and more. Any more feedback? The more you tell us the more we can help.
Can you help us improve? Resolved my issue. Clear instructions. Easy to follow. No jargon. Pictures helped. Didn't match my screen. Incorrect instructions. Too technical. Not enough information. Not enough pictures.
0コメント