24/06/2026
Advanced Excel questions and answers relevant to AP work.
1. What is XLOOKUP and how is it used in Accounts Payable?
Answer:
XLOOKUP searches for a value in a range and returns a corresponding value from another range.
Example:
Match Vendor ID with Vendor Name.
=XLOOKUP(A2,$F$2:$F$100,$G$2:$G$100,"Not Found")
AP Use:
Vendor master validation
Invoice lookup
Payment status tracking
2. What is the difference between VLOOKUP and XLOOKUP?
Answer:
VLOOKUP XLOOKUP
Searches left to right only Searches any direction
Requires column number Uses return array
Breaks if columns are inserted More flexible
Older function Newer function
Interview Tip:
Mention that XLOOKUP is preferred when available.
3. How do you identify duplicate invoices?
Answer:
Using COUNTIF:
=COUNTIF(A:A,A2)>1
Or Conditional Formatting:
Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values
AP Use:
Prevent duplicate payments.
4. How do you reconcile vendor statements using Excel?
Answer:
Use:
XLOOKUP/VLOOKUP
Conditional Formatting
Pivot Tables
Formula example:
=XLOOKUP(A2,CompanyData!A:A,CompanyData!B:B,"Missing")
This identifies invoices present in the vendor statement but missing from company records.
5. What is a Pivot Table and how is it used in AP?
Answer:
Pivot Tables summarize large data quickly.
AP Use Cases:
Outstanding invoices by vendor
Monthly payment analysis
Spend by supplier
Aging analysis
Steps:
Select data.
Insert → Pivot Table.
Drag fields into Rows, Columns, and Values.
6. How do you calculate invoice aging?
Answer:
=TODAY()-A2
Where A2 contains the invoice date.
Aging bucket:
=IF(B2
