Excel + AI: From Basic to Boss Level
Covers Past the VLOOKUP Ceiling: XLOOKUP and INDEX-MATCH, Structure First: Tables, and the PivotTables They Unlock, Power Query: Clean It Once, Refresh It Forever and more.
“Excel + AI: From Basic to Boss Level” is a 4-lesson course at Intermediate, created with soclever, a personal AI teacher. Each lesson takes a few minutes and ends with a check-in question; finish the curriculum and you can take a certificate test to earn a diploma. Try the first lesson without an account — or make your own course on any topic.
What you'll learn
- Past the VLOOKUP Ceiling: XLOOKUP and INDEX-MATCH. VLOOKUP breaks for a reason you can name: it finds your answer by counting columns, so its third argument is a number, not a meaning.
- Structure First: Tables, and the PivotTables They Unlock. A PivotTable that reports confidently wrong numbers is almost always sitting on badly shaped data rather than a broken PivotTable.
- Power Query: Clean It Once, Refresh It Forever. Power Query records your cleanup as an ordered list of steps and replays it on command, which turns an hour of monthly repair into a single refresh.
- AI as Your Formula Partner (and the Rule That Keeps You Safe). AI returns a plausible formula from a vague description and a correct one from a precise description, and the whole difference is what you say about the shape of your data.
Lesson 1: Past the VLOOKUP Ceiling: XLOOKUP and INDEX-MATCH
VLOOKUP breaks for a reason you can name: it finds your answer by counting columns, so its third argument is a number, not a meaning. Insert a column anywhere in that range and the formula keeps running while returning the wrong field.
It also only looks rightward. Whatever you search for has to sit to the left of whatever you want back, which is why so many inherited files carry a duplicated ID column pasted in front of the data purely to keep old formulas alive.
XLOOKUP removes both problems by taking ranges instead of counts. Written as =XLOOKUP(A2, Orders[ID], Orders[Amount]), it names the column you search and the column you return, so inserting, deleting or reordering columns changes nothing. It matches exactly by default, and its fourth argument lets you replace #N/A with a message you chose.
INDEX-MATCH does the same job in older files where XLOOKUP is not available: =INDEX(Orders[Amount], MATCH(A2, Orders[ID], 0)). MATCH finds the position of your value, INDEX returns whatever sits at that position. The zero is the exact-match flag, and omitting it is how people end up with an approximate binary search quietly handing back a neighbouring row.
Rewrite one VLOOKUP in your own spreadsheet today and say out loud what each argument does. The formula that survives next quarter is not the clever one, it is the one whose arguments point at columns by name instead of by position.
Questions this course answers
How does XLOOKUP find your answer instead of counting columns?
XLOOKUP removes both problems by taking ranges instead of counts.
What does omitting the zero in INDEX-MATCH cause people to end up with?
Omitting the zero is how people end up with an approximate binary search quietly handing back a neighbouring row.
What does unpivoting do to twelve month columns across the top?
Unpivot collapses those twelve columns into two, one holding the month and one holding the value.
More courses to explore
- Russian for intermidiate — 3 lessons (Teen level).
- Eilat — 4 lessons (Teen level).
- John Denver's early career — 4 lessons (Teen level).
- The Night Manager — 4 lessons (Teen level).
Early-stage product — exploratory, no guaranteed results.