Rent Roll Audits: Fixing #N/A when matching units between a PM software export and Excel

During a rent roll audit or property acquisition, matching units between property management (PM) software exports (such as Yardi, RealPage, or AppFolio) and internal financial models almost always yields #N/A errors. The lookup formula—whether XLOOKUP, VLOOKUP, or INDEX/MATCH—fails to match unit identifiers that look identical on screen.

An #N/A error during a unit match means the lookup function cannot find the exact search key in the lookup array. In PM software audits, this rarely means the unit does not exist; instead, it is almost always caused by invisible formatting differences: trailing spaces, non-breaking web spaces, leading zeros dropped during CSV export, or numbers stored as text strings.

How to Fix #N/A Errors in Rent Roll Unit Matching

Fix #N/A errors during unit lookups by wrapping your lookup value and lookup column in the TRIM and CLEAN functions to eliminate hidden padding: =XLOOKUP(TRIM(A2), TRIM(Export!A:A), Export!B:B). If unit IDs contain numbers, standardize data types using TEXT(A2, "@") or coerce text to numbers with --A2 so both datasets match.

---

Why Unit Numbers Fail to Match

Property management databases and Excel interpret strings differently. When you export a rent roll, unit IDs are subjected to data formatting conversions that introduce invisible discrepancies:

PM Export Value:   "101 "   (5 characters: includes trailing space)
Excel Audit Value: "101"    (3 characters: standard text)
Lookup Evaluation: "101 " = "101" -> FALSE -> Returns #N/A

The four primary causes of matching failures in rent roll audits are:

1. Trailing and Leading Whitespace: Database reporting engines often pad unit numbers with spaces to right-align or left-align fields.
2. Non-Breaking Characters (ASCII 160): Web-based reporting engines (such as browser-based exports) use non-breaking spaces (  or character code 160) instead of regular spaces (character code 32). Standard TRIM does not remove these.
3. Data Type Mismatches (Text vs. Numeric): Units like 101, 102, and 103 may be stored as true integers in your audit sheet, but are exported as text strings from the software. Excel will not match the number 101 with the text "101".
4. Dropped Leading Zeros: Units like 0102 or 04-B are stripped of their initial zeros if opened directly as an unquoted CSV file.

---

Step-by-Step Diagnostic and Resolution Procedure

Step 1: Run the Exact Match Diagnostic

To prove whether your problem is an invisible string mismatch, pick a row showing #N/A. Find the corresponding unit in the PM export and run a direct comparison in an empty cell:

=A2=Export!A15

If this formula returns FALSE despite both cells showing the same unit number (e.g., “Unit 204”), check their lengths using the LEN function:

=LEN(A2)          -> Returns 3
=LEN(Export!A15)  -> Returns 4 or 5

If the lengths do not match, hidden characters are present.

Step 2: Strip Regular and Non-Breaking Spaces

Standard spaces are easily removed with TRIM, but exports from cloud property platforms often contain non-breaking web spaces (code 160).

To remove both standard and non-breaking spaces along with unprintable line breaks, apply this formula in a helper column on your PM export sheet:

=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))

• SUBSTITUTE(A2, CHAR(160), " ") converts web spaces into standard spaces.
• CLEAN(...) removes non-printable system codes (ASCII 0 through 31).
• TRIM(...) strips all leading and trailing whitespace.

Use this cleaned helper column as your lookup range. For a complete technical walkthrough on how hidden database characters disrupt search formulas, see #N/A because of Hidden Non-Printing Characters (CLEAN function fix) and #N/A in VLOOKUP: The “Trailing Space” Trap.

Step 3: Resolve Text vs. Number Type Discrepancies

If both cells return the same LEN value (for example, 3 characters) but =A2=Export!A15 still returns FALSE, the cells have different underlying data types.

Cell A2:          101    (Data Type: Number)
Cell Export!A15:  "101"  (Data Type: Text)

Coercing Text to Numbers

If your audit model uses numbers but the PM software exports text, coerce the text lookup value into a true number using double negation (--):

=XLOOKUP(--A2, Export!A:A, Export!C:C)

Coercing Numbers to Text

If your audit model uses text strings (for example, combining numbers and letters like 101, 102, 102A) but the export parsed pure numeric units as numbers:

=XLOOKUP(TEXT(A2, "@"), Export!A:A, Export!C:C)

For deeper troubleshooting on mixed data types across spreadsheets, consult #N/A when searching Numbers stored as Text and #N/A when searching Text stored as Numbers.

---

Step 4: Restoring Dropped Leading Zeros

When opening raw CSV exports from systems like Yardi or MRI, Excel’s default import engine drops leading zeros (converting "0101" to 101). If your master rent roll retains those zeros, all lookups fail with #N/A.

The Remediation Formula

If your property uses uniform four-digit unit numbers, reconstruct the dropped zeros:

=TEXT(A2, "0000")

If unit numbering schemes vary across the asset (e.g., building 1 has 3 digits, building 2 has 4 digits), do not open the CSV directly by double-clicking. Instead:

1. Open a blank Excel workbook.
2. Go to Data > Get Data > From File > From Text/CSV.
3. In the preview window, select Do not detect data types.
4. Click Load. This imports unit IDs as raw text, preserving leading zeros exactly as stored in the database.

---

Auditing and Reconciling Unmatched Records

Once formatting is standardized, legitimate audit discrepancies may remain (such as offline units, model apartments, or storage lockers that appear in one system but not the other).

Surfacing Missing Units with IFNA

Instead of leaving raw #N/A values in your audit schedule, wrap your match formula in IFNA to flag records clearly:

=IFNA(XLOOKUP(TRIM(A2), Cleaned_Export_Units, Cleaned_Export_Rent), "Unit Missing from PM Export")

After matching unit records and rental rates, verify that lease turnover dates are properly formatted. If move-out fields contain empty text values that break date calculations, refer to Occupancy Forecasting: Handling #VALUE! when dates for “Move-out” are blank.. Finally, ensure manual lease edits do not overwrite your core revenue formulas by implementing checks from Gross Potential Rent (GPR): Fixing “Formula Overwrites” that hide actual vacancy losses..

---