![How To Use Vlookup In Excel 2011 For Mac How To Use Vlookup In Excel 2011 For Mac](/uploads/1/2/5/4/125438851/535662118.png)
Join Dennis Taylor for an in-depth discussion in this video, Using VLOOKUP with large tables, part of Excel 2011 for the Mac: Advanced Formulas and Functions. LEARNING With lynda.com content.
Disclaimer: For this demo I am using Microsoft Excel for Mac 2011 version 14.4.7. Problem: How can I look-up the name of a person from one spreadsheet and get the corresponding ID from another spreadsheet?
Solution: We can use a VLOOKUP excel function. Let’s assume we have two spreadsheets:. One called Need Help.xlsx where we would like to get an ID for a corresponding name. Second one is called Lookup.xlsx, which we will use to look-up the corresponding ID to that name.
Sort both the tables by column A in ascending order. In need help.xlsx table click on cell B2 and type =vlookup and then select the function.
A formula builder will open, select cell A2 as per the screenshot below:. Then click on second field of the formula builder called tablearray and open the lookup.xlsx without closing the formula builder. Select all the columns and rows in lookup.xlsx table. See the screenshot below:. Now update these two fields:. Colindexnum = 2, because we are getting the userid values which are in second column. Rangelookup = 0 (zero), because we want to only get those UserIds where User Name is an exact match of Owner Name.
You will be redirected to n eed help.xlsx workbook with a value in column B2. Close the formula builder. See the screenshot below:. Click on cell B2 and press Shift+↓ key to select cells B3 and B4. Then press Control + D to copy the VLOOKUP formula you have build and apply it to cells B3 and B4. This will get you the UserIds of other two remaining people.
See the screenshot below:. You can use this methodology to lookup tens and thousands of records. Tip: Just make sure that there are no duplicate values in the column a of lookup.xlsx spreadsheet. Please add your comments or questions below.
I have a problem when using VLOOKUP in excel for MAC 2011. I am looking up for a value two column in different sheet, Column A is the one I want to check, Column B is the reference. =VLOOKUP(A1, Sheet2!$A$1:$B$1000,2,0) Some of boxes in Column A get a value return, but the other cannot. For those which cannot, when I retype the words inside the box, and I get the value returned. I try to copy the Column A and selectively paste with value only, but those cannot get a returned value remained the same. I guest there is something wrong with the format, but I cannot fix it.
Seems likely an issue with trailing spaces. With or without in the can be handled by adapting your formula to: =VLOOKUP('.'
![Tutorial Tutorial](/uploads/1/2/5/4/125438851/445201817.jpg)
&A1&'.' ,Sheet2!$A$1:$B$1000,2,0) (provided you don't have something like manifestation as well as station in ColumnA!). If your source data has leading or trailing spaces that your Tablearray does not then the recommended solution is to remove those by a formula such as: =TRIM(A1) copied down to suit and then that array inserted back to A1 with Paste Special, Values. Another possibility is that you may be trying to match a value to text that looks like a value (or vice versa).