Excel's VLOOKUP function:
Searches for a value in the first column of a table array and returns a value in the same row from another column in the table array

It sounds useful for reconciliation work, but look closer and there are several pitfalls to beware of:

1. Use VLOOKUP correctly

   VLOOKUP has two matching modes - exact and approximate. Ensure you know the difference and choose the correct one, or else your reconciliation could be wrong.

   The default mode for VLOOKUP is approximate - often not what you want. You should normally enter FALSE as the fourth parameter of VLOOKUP when reconciling.

2. VLOOKUP is one-way only

   You can use VLOOKUP to search for a matching row in another table, but it won't tell you whether the other table has rows that the first one doesn't.

   Therefore, you should always use two sets of VLOOKUP formulae, one set to find records from table A in table B, and one to find records from table B in table A. Only then can you piece together the complete picture.

3. VLOOKUP only shows precise matches

   VLOOKUP is unforgiving if your data is not perfect. If, for example, you try to match against a number stored as a text value, or if your text code has an extra space character at the end, then VLOOKUP will fail to match those records.

   Unfortunately, these are exactly the sorts of data ‘imperfections’ common on data imported to Excel. You may want to guard against these by using the TRIM() and VALUE() functions in Excel.

4. Error propagation

   When VLOOKUP fails to find a matching value it reports the #N/A error. This is a problem because the error propagates to any other formulae, such as SUM(), that you're likely using to capture your reconciliation results.

   You'll need to trap the error using IF() and ISNA() functions, so now your VLOOKUP formula looks more like this:

   =IF(ISNA(VLOOKUP(D34, 'Bank Statement'!$A$1:$F$724, 4, FALSE)), 0, VLOOKUP(D34, 'Bank Statement'!$A$1:$F$724, 4, FALSE))

5. VLOOKUP can't handle multiple matches

   VLOOKUP always reports only the first match it finds. If your data has two or more rows with the same matching code then:

   • VLOOKUP can't choose the correct one to match against
   • all but the first matching transaction is invisible and missing from your reconciliation results.

   The only way to get around this limitation is to search for duplicate codes, perhaps using the COUNTIF() function, and then use a different matching strategy (not VLOOKUP) for those rows.

6. VLOOKUP can't match one-to-many

   Since VLOOKUP ignores all but the first matching row, it's impossible to reconcile rows one-to-many or many-to-many with VLOOKUP.

   If your data matches one-to-many then you'll need to carry out a row grouping and summing operation first, perhaps using a Pivot Table.

7. VLOOKUP uses only one column

   VLOOKUP can't help directly if you need to match on two columns, such as ‘Customer Number’ and ‘Invoice Number’ for example.

   You may be able to resolve this by joining your two columns into one with the CONCATENATE() function and then using VLOOKUP on the result.

So, should you use VLOOKUP for reconciliation?

Isolist reconciliation software works in Excel but handles each of the above shortfalls correctly and automatically. Of course you can often use VLOOKUP, but it's more cost-effective, faster and more reliable to use Isolist.