Start a conversation ↗

Description

Excel — Lookup Functions: Matching Tables, Extracting and Validating Data

Three hours to reconcile two files automatically instead of comparing rows one by one

  • 0.43 days — 3 h
  • In-person or virtual
  • Intermediate
  • Up to 6 participants

Two exports have to be reconciled every month: orders on one side and invoicing on the other. Without a method, the reconciliation is done visually with manual copy-paste. When a lookup formula is attempted, it returns errors or missing values with no clear explanation of where they come from.

These 3 hours cover reconciliation from end to end for users who are already comfortable with formulas. Participants prepare their keys, write an exact-match lookup, remove column-position constraints by combining INDEX and MATCH, handle values that are not found, then check the result before sharing it.

Learning objectives

  • Structure two tables to enable reliable reconciliation
  • Write a vertical lookup formula on an exact value
  • Combine INDEX and MATCH functions to remove dependency on column position
  • Handle values that are not found and displayed errors
  • Check the outcome of a reconciliation before using it

What makes this programme different

A complete reconciliation between two exports carried through to completion during the session
Diagnosis of the most common causes of failure on a lookup key
A method for checking the reconciliation before the file is shared

Programme

1Lookup keys and data preparation

Why the formula returns nothing

  • The concept of a unique key and a reference table
  • Invisible discrepancies caused by spaces, formats and case
  • Numbers stored as text and dates not recognised correctly
  • Removing duplicates in both tables beforehand

2Writing lookup formulas

Extracting the right value

  • Vertical lookup on exact match and approximate match
  • Locking references and copying the formula across
  • Combining INDEX and MATCH
  • Lookups on multiple criteria
  • Extracting a column located to the left of the reference column

3Errors, checks and use of results

A result you can defend

  • Handling values that are not found
  • Distinguishing between missing data and a formula error
  • Counting matched rows and remaining rows
  • Highlighting discrepancies for analysis
  • Converting to values before sharing the file

Who is it for

Accounting officers, administrative staff and controllers who regularly reconcile several data files.

Prerequisites

Regular use of a spreadsheet application and the ability to write simple formulas independently are required.

Dates & locations

12 scheduled dates between November 2026 and December 2027. Seats are confirmed in the order enquiries are received.

November 2026

December 2026

January 2027

February 2027

March 2027

April 2027

May 2027

June 2027

September 2027

October 2027

November 2027

December 2027

None of these dates suit you? We open additional sessions on request, and any programme can be run privately for your team.

Practical details

Before the programme
Online positioning questionnaire. Your development objectives are shared with the trainer, who tailors the practical case studies to your context.
Teaching methods
Theoretical input, workshops and practical case studies. Digital course materials and method sheets provided.
Assessment
Multiple-choice tests and role-play exercises. Assessment of learning at the start and end of the programme, with immediate and 60-day follow-up evaluations.
After the programme
One year of access to the e-learning platform. Self-assessment of the skills acquired and a 30-day follow-up session with your trainer.
How to register
Registration online or on the basis of a quotation.
Lead time
11 working days after confirmation of registration.
Accessibility
Accessible to people of determination. Contact our accessibility coordinator to design a suitable solution: contact@mpf-academy.ae
Start dates
Rolling intake: in addition to the scheduled sessions, this programme can start on request.