EXCEL SOLUTIONS

Check which items from one list are in another

Find which ordered items arrived and which are missing.

IntermediateOperationsAbout 7 minutes

Sample data

Row	A	B
1	Ordered	Received
2	P-101	P-102
3	P-102	P-101
4	P-103	P-105
5	P-104	P-103
6	P-105

List A and list B (fictional) Row 1 holds the headings. All data is fictional. Dates are year-month-day.

Steps

Each formula is already filled in and calculated. Edit it and press Run to try your own version. Nothing is saved or uploaded.

Is the first ordered item (P-101) found in the Received list? MATCH returns a position.
Result2
ISNUMBER turns the position into TRUE or FALSE.
ResultTRUE
Write it in words.
ResultReceived
Check the fourth ordered item, P-104. It never arrived.
ResultMissing
Count the empty cells in the Received list.
Result1
Join the received items into one line.
ResultP-102, P-101, P-105, P-103

What to remember

MATCH with 0 plus ISNUMBER is the simplest way to ask “is this in that list?”. For long lists, MatchFlow does the same job on whole files.

Formulas used

MATCHISNUMBERIF COUNTBLANKTEXTJOIN

Tools that help

MatchFlowCompare purchase orders, invoices and goods received lists: missing items, extra items, quantity and price differences.

Need Something Different? Let’s Talk.

Looking for different features or something built around your workflow? Share your idea with us.

Next lesson: Measure marketing campaign results Let’s Talk All tutorials