complete management software

This page describes the process of using the "VLOOKUP" function in Excel to translate data within uManage. 

This function is handy when importing data from an external source (such as a list of tenants to be added to existing accounts or a list of Bank ACH payments) into uManage.  The import process requires the uManage AccountId as an input field whereas the external source would have some other value that uniquely identifies the account (external source id) such as an address or a file number.  The VLOOKUP function can be used to translate the external source id into the uManage AccountId. If additional information is needed,  click this link    

The steps to accomplish this translation are (for this example we'll assume we have an external source id consisting of unit addresses):

1. Get a list of uManage AccountIds and unit addresses into Excel
   1. Go to Association | Manage Account.
   2. Select the association and set the page size so all accounts are listed on one page.
   3. Click the Export to CSV icon to export the entire page to Excel.  Note, CSV exports the data without formatting.
   4. Open the file in Excel and delete all columns except for AccountId and Unit Address
   5. Move the UnitAddress so it is in Column A and AccountId is in Column B
   6. Sort Columns A and B on Column A.  This is important as the VLOOKUP function needs a sorted list.
   7. This is Sheet1
2. Open your external source file in Excel.  This file should contain a column for the Unit Address (column A).  
   1. Remove all columns except for Unit Address.  This is your 'dataset.'
   2. Copy this entire dataset into a new Sheet2 in the workbook from step 1 above.  
3. Go back to the 1st sheet in the external source Excel file.
   1. Add the VLOOKUP function in column C. 
   2. Copy the function down to the remaining cells in the worksheet
   3. The function should return the AccountId from Sheet 2.  If a match is not found the function returns a #VALUE.  You will need to investigate the reason for the not found. 

There are four pieces of information that you will need in order to build the VLOOKUP syntax:

1. The value to look up also called the lookup value.
2. The range where the lookup value is located.  Remember that the lookup value should always be in the first column in the range for VLOOKUP to work correctly.  Example: if the lookup value is in cell C2, then the range should start with C.  When specifying a range, it may be necessary to indicate a lock on the desired columns by inserting a dollar sign between the column letter and the number of the cell.  If your range is C1:D25, enter this as C$1:D$25 in the range field of the formula.  Remember to use the $ to fix the cell range.
3. The column number in the range that contains the return value.  Example:  C1:D25 as the range, should count D as the second column and so the value to enter in the VLOOKUP formula is "2" to specify the second column.
4. IMPORTANT:  Always specify FALSE in the Range_lookup field to ensure an exact match of the return value.  Do not leave this field blank.

Now put all of the above together as follows:

=VLOOKUP (lookup value, range containing the lookup value, the column number in the range containing the return value, Approximate match (TRUE) or Exact match (FALSE)).