Administration and Executive AssistantTier 320 minintermediate

Stock Count Variances with XLOOKUP

Match a shelf count to the system and find what is short, over or missing from the records.

What you will learn

• Look up the system quantity for each counted code with an exact match
• Return 0 for a code that is not on the system, instead of #N/A
• Work out counted minus system, and the net variance

Excel skills

XLOOKUP if_not_found, SUMIFS, variance · Exact-Match Lookups

The task

We counted the shelves this morning. The Count tab has what we counted, in shelf order. Bring in the system quantity for each code from the System tab (column C), work out the variance (counted minus system) in column D, and put the net variance on the Summary tab.

You work in a real spreadsheet in your browser, and your answer is checked against hidden traps the moment you submit.

Try this lesson free