Saturday, May 19, 2012

How to VLookup Two Values in Excel 2007

1. Open the Excel 2007 file that contains the table you want to work with.
2. Right-click on the column letter above the leftmost column on your table. Choose 'Insert' to create a new column.
3. Select the top cell in your new column. Type '=XXYY' into the cell, where 'XX' equals the cell reference to the first cell in the first column you want to search through with VLookup, and 'YY' is the second column. Press 'Enter' and you will see the entries in those columns compressed together into the new column. For example, if 'here' was in the first column, and 'there' was in the second, your new column would read 'herethere.'
4. Move your mouse over the bottom-right corner of the cell with your formula. Click the mouse and hold down the button,and then drag the mouse down until your reach the last row in your table. Release the mouse button and Excel will copy your formula down the entire length of the table.
5. Click on the top cell in your new column. Press 'Ctrl' and 'A' to select the entire table. Click the 'Home' tab at the top of the screen. Click the 'Sort and Filter' button, which you can find in the 'Editing' area of the ribbon. Choose 'Sort A to Z.' VLookup will not return good values if the first column is not ordered from lowest to highest.
6. Select any cell outside of the data table. Enter the following formula into the cell: =VLOOKUP('XX',YY:ZZ,2) where 'XX' is equal to the two values that you want to search for, 'YY' is the top-left cell in the data table, 'ZZ' is the bottom-right cell in the data table and '2' is the column number where you want to search for your result. When entering your search values, remember to place them together without spaces, so if you were searching for the number '1' in one column and the word 'trucks' in a second column, you would enter '1trucks.' Press 'Enter' to complete the formula and get your result.

Blogger news