A Deep Dive Into Excel’s Time and Date Functions

Because time is money, and you’re currently losing both by not knowing these.

Let me paint you a picture.

It’s Monday morning. You have a spreadsheet. The spreadsheet has dates in it. Someone — let’s call them “Past You” — has entered those dates in seventeen different formats, across three different columns, using a mix of text strings, actual date values, and what appears to be a cry for help disguised as a number.

You need to calculate how many days until a project deadline. You need to extract the month. You need to figure out whether a delivery happened on a weekend. And you need all of this before the 9am standup, which is in four minutes.

This is the moment Excel’s date and time functions were born for. And yet — most people know maybe three of them.

Not you. Not anymore. By the end of this article, you’re going to know all of them, how they work, when to use them, and why someone who doesn’t know them is basically navigating a city without GPS and too proud to ask for directions.

Let’s go.

First, A Two-Minute Explanation That Will Save You Hours

Before we dive in, here’s the thing most people never explain about dates in Excel: Excel doesn’t actually store dates as dates.

Seriously. Under the hood, every date is just a number. January 1, 1900 = 1. January 2, 1900 = 2. Today (April 16, 2026) = some number in the 46,000s. Excel just formats those numbers to look like dates, which is very considerate of it.

Why does this matter? Because once you understand that dates are numbers, everything about date functions suddenly makes logical sense. Adding 7 to a date gives you next week. Subtracting two dates gives you the number of days between them. The formula isn’t doing anything mystical — it’s just arithmetic with a nice coat of paint.

Times work the same way, but as decimal fractions of a day. Noon (12:00) = 0.5. 6am = 0.25. Midnight = 0. Got it? Good. Now let’s talk functions.

The “What Day/Time Is It Right Now?” Functions

TODAY()

What it does: Returns today’s date. Recalculates every time the file opens or refreshes.

Syntax: =TODAY()

Real-life example: You’re tracking a project deadline in B2 (let’s say it’s May 30, 2026). In C2 you put =B2-TODAY() and now you always know, instantly and without doing math in your head, exactly how many days you have left. Which might be motivating. Or terrifying. Either way, accurate.

💡 Pro Tip: TODAY() is “volatile” — it recalculates constantly. If you need a permanent timestamp of when something was entered, use Ctrl+; instead, which hard-codes the date and never changes.

NOW()

What it does: Returns the current date and time down to the minute. Also recalculates every time the sheet refreshes.

Syntax: =NOW()

Real-life example: You’re building a log of customer support tickets. Every time a new ticket is submitted, you want to record the exact timestamp. =NOW() in the timestamp column gives you something like “16/04/2026 09:47” — date and time, together, in one cell. Extremely useful when “when was this submitted?” is a question that leads to arguments.

💡 Pro Tip: NOW() returns a number with decimals. If you subtract NOW() from a future date and you get a weird decimal, that’s because it’s including the fractional time of day. Use INT(NOW()) to strip the time and get just today’s date as a whole number.

The “Build a Date From Parts” Functions

DATE(year, month, day)

What it does: Constructs a proper Excel date from three separate numbers — a year, a month, and a day. This is your rescue function when someone has helpfully stored the year in column A, the month in column B, and the day in column C.

Real-life example: You have an export from an ancient database system. Column A says “2026”, column B says “4”, column C says “16”. None of it is usable as a date yet. =DATE(A2,B2,C2) stitches them together into April 16, 2026 — a real, actual Excel date you can calculate with.

💡 Pro Tip: DATE() handles overflow beautifully. =DATE(2026,13,1) doesn’t give you an error — it gives you January 1, 2027, because month 13 just rolls into the next year. This is wildly useful for adding months to dates without weird edge cases.

TIME(hour, minute, second)

What it does: Same concept as DATE(), but for time. Constructs an Excel time value from separate hour, minute, and second components.

Real-life example: You’re managing shift schedules. In your system, start times come in as three separate columns: 9 (hour), 30 (minute), 0 (second). =TIME(A2,B2,C2) combines them into 9:30:00 AM — a real time value you can then use for duration calculations, comparisons, and scheduling logic.

💡 Pro Tip: Need to add 90 minutes to a time? Don’t add 90. Add TIME(0,90,0). Excel will handle the rollover correctly. Adding the raw number 90 will add 90 days, not minutes, and you’ll have a very confusing schedule.

The “Tear a Date Apart” Functions

YEAR(date)

What it does: Extracts just the year from a date. Returns a four-digit number.

Real-life example: You have a column of transaction dates going back five years and you want to create a pivot table by year. Add a helper column with =YEAR(A2) and suddenly you have a clean “Year” field to group on. Simple, extremely useful, used approximately nine million times a day in spreadsheets around the world.

MONTH(date)

What it does: Extracts the month number (1 through 12) from a date.

Real-life example: Your sales report needs a “Month” column for filtering. =MONTH(A2) gives you a number. Combine with =TEXT(A2,"MMMM") if you want the actual month name instead. Report looks good, boss is happy, you leave on time. This is the dream.

💡 Pro Tip: To convert a month number back to a name, use =TEXT(DATE(2000,A2,1),"MMMM"). Classic Excel party trick.

DAY(date)

What it does: Extracts the day of the month (1 through 31) from a date.

Real-life example: You need to flag all invoices issued on the last day of the month. =DAY(A2)=DAY(EOMONTH(A2,0)) — yes, we’ll get to EOMONTH shortly — checks whether the day of an invoice date equals the last day of its month. Month-end billing logic, solved.

HOUR(time), MINUTE(time), SECOND(time)

What they do: Extract the hour, minute, or second from a time value, respectively.

Real-life example (all three): You’re analyzing customer call durations. The start time is in A2, end time in B2, and the duration is in C2 as a time value. Now you need this in a readable format. =HOUR(C2)&"h "&MINUTE(C2)&"m "&SECOND(C2)&"s" turns 0:47:23 into “0h 47m 23s.” Your operations manager will use words like “professional” and “organised.” You will nod calmly.

The “What Day of the Week Is This?” Functions

WEEKDAY(date, [return_type])

What it does: Returns a number representing the day of the week for a given date. The return_type argument controls the numbering system (1=Sunday through Saturday, 2=Monday through Sunday, etc.). Default is 1.

Real-life example: You’re scheduling automated reports and need to make sure nothing sends on a weekend. =WEEKDAY(A2,2) returns 6 for Saturday and 7 for Sunday (Monday-based numbering). Wrap it in an IF: =IF(WEEKDAY(A2,2)>5,"Weekend","Weekday") and you’ve got an instant flag. No more reports going out on a Saturday to an inbox nobody checks.

💡 Pro Tip: Use return_type = 2 (Monday = 1, Sunday = 7) if you’re in Europe or working on business logic that treats Monday as the start of the week. This avoids a lot of off-by-one headaches.

WEEKNUM(date, [return_type])

What it does: Returns the week number of the year for a given date. Week 1 is the week containing January 1st. The return_type argument controls which day the week starts on.

Real-life example: You’re reporting on weekly sales performance and your stakeholders reference “Week 15” and “Week 28” instead of actual dates (because they’re creatures of habit and you’ve accepted this). =WEEKNUM(A2) gives you the ISO week number, which you can use to group, sort, and slice your data by week without ever writing a formula that involves seven nested IFs. Everyone who has written seven nested IFs to solve this problem knows the pain being avoided here.

ISOWEEKNUM(date)

What it does: Returns the ISO week number — the international standard used in Europe and most professional business contexts. ISO weeks always start on Monday and the first week of the year is the one containing the first Thursday.

Real-life example: You’re working with a European partner who refers to “KW16” (Kalenderwoche 16 — German for calendar week 16). =ISOWEEKNUM(A2) gets you on the same page without having to explain to your German colleague why your week numbers are off by one. Cross-border collaboration, sorted.

The “How Much Time Has Passed?” Functions

DATEDIF(start_date, end_date, unit)

What it does: Calculates the difference between two dates in a specified unit: “Y” for years, “M” for months, “D” for days, “YM” for months excluding years, “MD” for days excluding months, “YD” for days excluding years.

This function is technically undocumented by Microsoft. It exists, it works perfectly, but Microsoft didn’t include it in their official function list for mysterious reasons that nobody has ever fully explained. It’s the Excel equivalent of a secret room.

Real-life example: You’re calculating employee tenure for an HR report. =DATEDIF(A2,TODAY(),"Y") gives you complete years of service. Want to be precise? =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months" gives you “3 years, 7 months.” The kind of thing that makes an HR system look polished and thorough. Very satisfying.

💡 Pro Tip: Always make sure end_date is greater than start_date. DATEDIF throws an error if the dates are the wrong way around and, unlike most Excel errors, it is not subtle about it.

DAYS(end_date, start_date)

What it does: Returns the number of days between two dates. Cleaner than subtraction for some scenarios.

Real-life example: Subscription renewal tracking. Column A has sign-up dates, column B has today’s date. =DAYS(B2,A2) gives you subscription age in days. When this hits 365, you know it’s renewal time. Simple, readable, does exactly one thing.

DAYS360(start_date, end_date, [method])

What it does: Calculates days between two dates based on a 360-day year (twelve 30-day months). This is a financial standard, not a mistake.

Real-life example: You’re working in accounting and calculating interest on a loan or bond using the 30/360 day count convention that the financial industry uses. =DAYS360(A2,B2) gives you the “banking days” between two dates. If someone in finance handed you a spreadsheet using this function and you thought it was a typo — it wasn’t.

💡 Pro Tip: The optional method argument is for US (FALSE) vs. European (TRUE) day count conventions. Yes, there are two. Yes, this matters in international financial documents. Yes, getting them mixed up is awkward.

NETWORKDAYS(start_date, end_date, [holidays])

What it does: Returns the number of working days (Monday–Friday) between two dates. You can optionally pass a range of holiday dates to exclude those too.

Real-life example: Project management gold. Client signs off on a project on April 16. The deliverable is due May 16. =NETWORKDAYS(A2,B2,HolidayRange) tells you how many actual working days you have, excluding weekends and any public holidays you’ve listed. This number will either reassure you or cause a mild crisis. Either outcome is useful information.

💡 Pro Tip: Build a dedicated “Holidays” named range in your workbook and reference it in every NETWORKDAYS formula. When holidays change next year, you update one list and every formula updates automatically. This is the kind of forward-thinking that makes colleagues ask if you took a course somewhere.

NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

What it does: Same as NETWORKDAYS but lets you define which days are the “weekend.” Because not everyone works a Monday–Friday schedule, and Excel knows this.

Real-life example: You’re calculating delivery times for a logistics company where the drivers work Tuesday–Saturday and have Sunday–Monday off. =NETWORKDAYS.INTL(A2,B2,2,HolidayRange) with weekend code “2” means Monday and Tuesday are treated as non-working days. No more manually adjusting day counts because the formula assumed a Western workweek that your operations don’t follow.

💡 Pro Tip: The weekend argument accepts both number codes (1–7, 11–17) and a 7-character string like “1000001” where each character represents Mon–Sun and “1” means non-working day. The string method is more flexible for unusual schedules.

WORKDAY(start_date, days, [holidays])

What it does: Returns the date that is a certain number of working days after (or before, with a negative number) a start date. Skips weekends automatically.

Real-life example: An order is placed on April 16. Your SLA promises delivery within 5 business days. =WORKDAY(A2,5,HolidayRange) returns April 23 — which is the correct delivery deadline after skipping the weekend. Much better than telling a customer their order arrives “in 5 days” and then having them show up on Saturday to collect it.

WORKDAY.INTL(start_date, days, [weekend], [holidays])

What it does: WORKDAY with the same custom weekend flexibility as NETWORKDAYS.INTL.

Real-life example: Same logistics scenario as above, but now you’re calculating the dispatch date by counting backwards. =WORKDAY.INTL(DeadlineDate,-3,2) tells you: if this needs to arrive by X, we need to ship by Y, on a Tuesday-Saturday schedule. Operations managers tend to love people who think this way.

The “Find The Start or End of a Period” Functions

EOMONTH(start_date, months)

What it does: Returns the last day of the month that is a specified number of months before or after a date. Months = 0 gives you the last day of the current month.

Real-life example: You’re calculating invoice due dates set to the last day of the month following the billing date. =EOMONTH(A2,1) returns the last day of the month after any given billing date. January invoice? Due February 28 (or 29 in a leap year — Excel handles this correctly without you doing anything heroic). This alone is worth the price of knowing EOMONTH.

💡 Pro Tip: EOMONTH(A2,0)+1 gives you the first day of the next month. Because the day after the last day of a month is always the first day of the next one. Simple math, endlessly useful.

EDATE(start_date, months)

What it does: Returns the date that is exactly a specified number of months before or after a given date. Unlike adding 30, this respects the actual calendar.

Real-life example: Subscription renewals. Customer signs up on January 31. One month later is… February 28 (or 29). Not March 2. =EDATE(A2,1) handles this correctly where =A2+30 does not. If your subscription billing is off by a few days every month, there’s a good chance someone used A2+30 somewhere. Go find it.

💡 Pro Tip: Use negative values to go backwards in time. =EDATE(A2,-6) gives you the date six months before A2. Useful for calculating notice periods, lookback windows, and “when should we have started worrying about this.”

The “Convert Text to Actual Dates” Functions

DATEVALUE(date_text)

What it does: Converts a date stored as text into a proper Excel serial number (real date). Essential when importing data from systems that export dates as strings.

Real-life example: Your CRM export gives you dates like “April 16, 2026” stored as text. Excel won’t calculate with them. =DATEVALUE("April 16, 2026") converts it into a real date value you can actually use. Then format the cell as a date, and you’re back in business.

💡 Pro Tip: If your text dates are in a regional format Excel doesn’t recognize, DATEVALUE may give you an error. This is Excel’s polite way of saying “I have no idea what 16/04/2026 means in your locale.” In that case, combine MID, LEFT, RIGHT, and DATE to manually parse the components. Not glamorous, but effective.

TIMEVALUE(time_text)

What it does: Converts a time stored as text into an Excel decimal time value.

Real-life example: Your helpdesk system exports call times as text strings like “09:47:00 AM.” =TIMEVALUE("09:47:00 AM") returns 0.406944… which Excel recognizes as a time and can calculate with. Now you can compute average call duration, flag calls over a certain length, and build actual operational reporting instead of just staring at a column of text.

The “Format and Display” Functions

TEXT(value, format_text)

What it does: Converts any number or date into a text string with a specified format. Technically a general-purpose function, but utterly indispensable for dates.

Real-life example: You’re building an automated email subject line in a formula. You want it to say “Weekly Report – Week 16, April 2026.” ="Weekly Report – Week "&WEEKNUM(TODAY())&", "&TEXT(TODAY(),"MMMM YYYY") produces exactly that, updating itself every week. Your reports look like they were crafted by hand. They were not. That’s the whole point.

💡 Pro Tip: Common date format codes — “D” = day number, “DD” = zero-padded day, “DDD” = Mon/Tue/Wed, “DDDD” = Monday/Tuesday…, “MMM” = Jan/Feb/Mar, “MMMM” = January/February…, “YYYY” = four-digit year. Combine freely. Format to taste.

The “Rounding Times” Functions

FLOOR(number, significance) and CEILING(number, significance)

What they do: Round a time (or date) down (FLOOR) or up (CEILING) to the nearest specified interval.

Real-life example: You’re calculating billable hours and your company bills in 15-minute increments. A call lasts 47 minutes. Rounded up to the nearest quarter hour: =CEILING(A2,TIME(0,15,0)) gives you 1:00 — one hour billed. Rounded down: =FLOOR(A2,TIME(0,15,0)) gives you 0:45. Your billing system is now as pedantic as your accountant. That’s a feature.

MROUND(number, multiple)

What it does: Rounds a number to the nearest specified multiple. Works on times too.

Real-life example: You want to round all meeting times to the nearest 30-minute slot for a cleaner schedule view. =MROUND(A2,TIME(0,30,0)) rounds 10:17 to 10:00 and rounds 10:22 to 10:30. Schedule looks neat, everybody thinks you planned it this way. You absolutely did not plan it this way.

The Less-Famous Ones (That Are Secretly Brilliant)

YEARFRAC(start_date, end_date, [basis])

What it does: Returns the fraction of a year between two dates. The basis argument controls the day-count convention (actual/actual, 30/360, etc.).

Real-life example: You need to calculate pro-rated annual fees for a service contract that started mid-year. =YEARFRAC(ContractStart,ContractEnd,1) gives you something like 0.623 — meaning 62.3% of the year was covered. Multiply by the annual fee and you have the exact pro-rated amount. Finance teams use this constantly. Most people have never heard of it.

DATESTRING(date)

What it does: Converts a date to a text string in the system locale format. Note: this is a legacy function and availability varies by Excel version.

Real-life example: Mostly used in older workbooks or specific regional output requirements. You’re better off using =TEXT(A2,"DD/MM/YYYY") and controlling the format yourself. But if you encounter DATESTRING in someone else’s spreadsheet, now you know what it does instead of quietly panicking.

Putting It All Together: The Formula That Impresses Everyone

Want a formula that shows off everything? Here’s one for a project status dashboard.

Assume A2 is a project start date, B2 is a planned end date, and today is TODAY().

="Project is "&DATEDIF(A2,TODAY(),"D")&" days in. "&NETWORKDAYS(TODAY(),B2)&" working days remaining, due "&TEXT(B2,"DDDD, MMMM D")&IF(WEEKDAY(B2,2)>5," (⚠️ deadline falls on a weekend!)",".")

This one formula tells you how long the project has been running, how many working days are left, the deadline in plain English, and flags it if the deadline lands on a weekend. It updates every day automatically.

Is it a bit much for a Monday morning? Possibly. Does it make you look like an Excel wizard? Absolutely.

The Bottom Line

Excel’s date and time functions aren’t just a list of things to memorize. They’re a toolkit for turning raw timestamps into actual, actionable information — deadlines, durations, schedules, billing, planning, reporting.

Most people use three of them. Today you learned all of them. That gap between “most people” and you? That’s your edge.

Now go open a spreadsheet and do something useful with it. The deadline is closer than you think — and thanks to NETWORKDAYS, you now know exactly how much closer.

Here’s a cheatsheet for your troubles: https://blog.stibelman.com/excel-date-time-functions-cheatsheet/

Shay Stibelman is a digital consultant based in Milan, Italy. He helps businesses work smarter with the tools they already pay for — and occasionally writes 4,000-word articles about Excel dates because someone has to, and he has a thing about people losing time to easily solvable problems.

CERCA.VERT e CERCA.X: La Guida Definitiva

Tutto quello che avreste voluto sapere, spiegato in modo umano, con esempi veri, e senza farvi piangere davanti al foglio Excel.

Ogni anno, milioni di persone aprono Excel, guardano due tabelle separate, e pensano: “ci deve essere un modo per collegare queste cose senza copiarle a mano una per una.” C’è. Si chiama CERCA.VERT. E da qualche anno c’è anche CERCA.X, che è la stessa cosa ma senza i difetti imbarazzanti. Questa è la guida che nessuno vi ha mai fatto leggere prima di lasciarvi soli con un #N/D e un martedì pomeriggio distrutto.

CERCA.VERT: il classico che non passa mai di moda (ma che fa i capricci)

CERCA.VERT significa Cerca Verticalmente. In pratica: date un valore, vi va a cercarlo in una colonna di una tabella, e vi restituisce il valore corrispondente da un’altra colonna della stessa riga. Come un cameriere che cerca il vostro nome sulla lista prenotazioni e vi dice a che tavolo siete seduti.

Semplice in teoria. In pratica, come vedremo, quel cameriere a volte vi dice #N/D e sparisce in cucina.

La sintassi completa

CERCA.VERT(valore; matrice_tabella; indice_colonna; [corrisp_intervallo])

Parametro	Cosa fa	Esempio
valore – obbligatorio	Il valore che volete cercare. Può essere un testo, un numero, o un riferimento a cella.	`A2` (la cella che contiene il codice dipendente)
matrice_tabella – obbligatorio	La tabella in cui cercare. Attenzione: la colonna di ricerca DEVE essere la prima colonna (quella più a sinistra) di questa selezione.	`B2:E500` oppure `TabellaHR[#Tutto]`
indice_colonna – obbligatorio	Il numero della colonna della tabella da cui prendere il risultato. Se la tabella ha 4 colonne e volete la terza, scrivete 3.	`3` (restituisce il valore dalla 3ª colonna)
corrisp_intervallo facoltativo	`FALSO` = cerca corrispondenza esatta (quasi sempre quello che volete). `VERO` = cerca corrispondenza approssimativa (utile per scaglioni, es. tasse, fasce di punteggio). Default: `VERO`, quindi scrivete sempre FALSO se non siete sicuri.	`FALSO`

Trappola classica

Il parametro corrisp_intervallo ha default VERO. Questo significa che se ve lo dimenticate, Excel farà una ricerca approssimativa. Su dati non ordinati, questo produce risultati sbagliati senza avvisarvi. Senza errore. In silenzio. Come un collega che ha capito male le istruzioni ma non lo ammette.

Esempio base di CERCA.VERT

CERCA.VERT(A2; TabellaStipendi; 3; FALSO)
→ Cerca il codice in A2 nella prima colonna di TabellaStipendi, restituisce il valore dalla 3ª colonna

Pro Tip

Usate sempre i riferimenti di tabella strutturata (Tabella1[Colonna]) invece dei range rigidi (B2:E500). Se aggiungete righe alla tabella, la formula si aggiorna da sola. Se usate il range rigido, dovete ricordarvi di aggiornarlo manualmente, e sicuramente ve ne dimenticherete nel momento peggiore.

CERCA.X: il fratello minore che è cresciuto meglio

CERCA.X è arrivato nel 2019 con Microsoft 365 e ha risolto praticamente tutti i problemi strutturali di CERCA.VERT. Non è solo “un CERCA.VERT più nuovo” — è una funzione completamente ripensata che funziona in modo diverso, più flessibile e più umano.

Se CERCA.VERT è il cameriere che vi cerca sulla lista dal basso verso l’alto e può guardare solo a destra, CERCA.X è il maitre che può guardare ovunque, dirvi se non siete in lista senza fare scene, e persino cercare partendo dalla fine.

La sintassi completa

CERCA.X(valore; matrice_ricerca; matrice_restituzione; [se_non_trovato]; [modalità_corrispondenza]; [modalità_ricerca])

Parametro	Cosa fa	Esempio
valore obbligatorio	Il valore che volete trovare. Identico a CERCA.VERT.	`A2`
matrice_ricerca obbligatorio	La colonna (o riga) in cui cercare. Non deve essere per forza la prima. Può essere qualsiasi colonna della tabella.	`TabellaClienti[ID]`
matrice_restituzione obbligatorio	La colonna (o riga) da cui prendere il risultato. Completamente separata dalla colonna di ricerca. Può essere a sinistra, a destra, ovunque.	`TabellaClienti[NomeCliente]`
se_non_trovato facoltativo	Cosa restituire se il valore non viene trovato. Se omesso, restituisce #N/D. Con questo parametro, potete restituire testo personalizzato, zero, o qualsiasi altra cosa.	`"Non trovato"` oppure `0`
modalità_corrispondenza facoltativo	`0` = corrispondenza esatta (default). `-1` = esatta o prossimo minore. `1` = esatta o prossimo maggiore. `2` = con caratteri jolly (*, ?).	`0`
modalità_ricerca facoltativo	`1` = dalla prima all’ultima riga (default). `-1` = dall’ultima alla prima (utile se volete l’occorrenza più recente). `2` o `-2` = ricerca binaria (su dati ordinati, molto più veloce).	`-1` (per trovare la riga più recente)

Pro Tip

Il parametro se_non_trovato da solo vale già il passaggio a CERCA.X. Il vecchio SE.ERRORE(CERCA.VERT(...); "Non trovato") è verbose e ripete la formula due volte. Con CERCA.X lo scrivete una volta sola, pulito, leggibile, e i vostri colleghi vi guarderanno con rispetto.

CERCA.X(A2; TabellaClienti[ID]; TabellaClienti[NomeCliente]; "Cliente non registrato")
→ Cerca A2 nella colonna ID, restituisce il NomeCliente. Se non esiste: "Cliente non registrato"

Quando usare uno e quando usare l’altro: 4 esempi reali

La risposta breve: usate sempre CERCA.X se il vostro Excel lo supporta. Ma la vita è complicata, i colleghi usano versioni vecchie di Office, e a volte esistono motivi legittimi per cui CERCA.VERT è ancora la scelta giusta. Eccoli.

CERCA.VERT · Limiti

La colonna di ricerca deve essere la prima (a sinistra)
Si riferisce alle colonne per numero, non per nome
Nessuna gestione nativa degli errori
Non cerca da destra verso sinistra
Non trova l’ultima occorrenza

CERCA.X · Vantaggi

Cerca in qualsiasi colonna, restituisce da qualsiasi colonna
Usa nomi di colonna, non numeri arbitrari
Gestione errori integrata con se_non_trovato
Cerca in entrambe le direzioni
Trova l’ultima occorrenza con modalità -1

Esempio 1 · Biblioteca · Vince CERCA.VERT
File condiviso con colleghi su Excel 2016

La bibliotecaria ha un catalogo con 8.000 libri: codice ISBN nella colonna A, titolo in B, autore in C, reparto in D. Deve fare un file che anche la collega part-time — che usa Excel 2016 sul computer fisso del piano -1 — possa aprire e usare senza errori.

CERCA.X non esiste in Excel 2016. Se la bibliotecaria usa CERCA.X, la collega vede solo un errore #NOME? su ogni cella. Possibile scenario di crisi diplomatica tra piani. Qui vince CERCA.VERT, punto.

Soluzione · CERCA.VERT (compatibilità)
CERCA.VERT(A2; Catalogo!A:D; 2; FALSO)
→ Cerca l'ISBN in A2, restituisce il titolo (colonna 2). Funziona su qualsiasi versione di Excel.

Pro Tip

Se non siete sicuri di quale versione di Excel usino i vostri colleghi, andate su File → Salva con nome e guardate se c’è un avviso di compatibilità. O chiedete direttamente. È socialmente accettabile.

Esempio 2 · Ambulatorio Medico · Vince CERCA.X
La colonna di ricerca non è la prima

L’amministrativa dell’ambulatorio ha una tabella pazienti: cognome in colonna A, nome in B, codice fiscale in C, medico di riferimento in D, data ultima visita in E. Deve cercare i pazienti per codice fiscale (colonna C) e restituire il medico (colonna D).

Con CERCA.VERT, la colonna di ricerca deve essere la prima. Il codice fiscale è in colonna C. Quindi dovrebbe spostare le colonne, rompendo probabilmente altri riferimenti, oppure usare il classico trucco INDEX+CONFRONTA. Oppure — semplicemente — usare CERCA.X che non ha questo problema.

Soluzione · CERCA.X (ricerca su qualsiasi colonna)
CERCA.X(F2; TabellaPazienti[CodiceFiscale]; TabellaPazienti[MedicoRiferimento]; "Paziente non trovato")
→ Cerca il CF in F2 nella colonna CF, restituisce il medico. Nessun bisogno di spostare colonne.

Esempio 3 · Scuola · Vince CERCA.X
Trovare l’ultima occorrenza (il voto più recente)

La segreteria scolastica tiene un registro voti con migliaia di righe: ogni volta che un professore inserisce un voto, aggiunge una riga. Lo stesso studente ha decine di righe. La dirigente vuole sapere l’ultimo voto inserito per ogni studente — cioè il più recente, che è l’occorrenza più in basso nella tabella.

CERCA.VERT trova sempre la prima occorrenza dall’alto. Non esiste modo nativo di fargli trovare l’ultima. Con CERCA.X basta impostare la modalità di ricerca a -1: cerca dall’ultima riga verso l’alto, quindi trova l’occorrenza più recente.

Soluzione · CERCA.X (ultima occorrenza)
CERCA.X(A2; RegistroVoti[IDStudente]; RegistroVoti[Voto]; "Nessun voto"; 0; -1)
→ Modalità -1: cerca dall'ultima riga. Restituisce il voto più recente dell'alunno.

Pro Tip

La modalità -1 di CERCA.X è anche utile negli help desk e nei magazzini per trovare l’ultima transazione o l’ultimo aggiornamento di stato su un ticket o un ordine. Ogni volta che avete dati temporali con duplicati e volete l’ultimo, pensate a CERCA.X con modalità -1.

Esempio 4 · Negozio / Punto Vendita · Vince CERCA.VERT
Tabelle semplificate con struttura fissa

Il responsabile del negozio ha una tabella prezzi con codice prodotto in colonna A e prezzo in colonna B. Due colonne. La usa da anni. La struttura non cambia mai. La deve inviare ogni settimana ai tre punti vendita che hanno tutti Excel diversi, incluso un tablet con Excel Mobile.

In questo caso CERCA.VERT è perfettamente adeguato e universalmente compatibile. Non c’è nessun vincolo di colonna da aggirare, nessuna necessità di gestione errori avanzata, nessuna ricerca inversa. Usare CERCA.X qui non aggiunge nulla, e rischia di rompere il file sui dispositivi più vecchi. Semplicità vince.

Soluzione · CERCA.VERT (semplicità e compatibilità)
CERCA.VERT(A2; ListinoPrezzi!A:B; 2; FALSO)
→ Cerca il codice prodotto, restituisce il prezzo. Funziona ovunque, anche su Excel Mobile.

“La funzione più giusta non è sempre la più nuova. È quella che risolve il problema senza crearne di nuovi.”

CERCA.X e CERCA.VERT dentro altre funzioni: 3 esempi

Finora abbiamo usato le funzioni CERCA da sole. Ma Excel diventa davvero potente quando iniziate a usarle come ingredienti all’interno di formule più grandi. Il valore cercato diventa l’input per un calcolo, una condizione, o un’altra funzione. Queste tre situazioni vi capiteranno quasi sicuramente.

Nidificazione 1 · Ufficio HR · SE + CERCA.X
Calcolo bonus condizionale basato sul livello del dipendente

Il reparto HR deve calcolare il bonus di fine anno. La regola: se il livello del dipendente (recuperato dalla tabella HR) è “Senior”, si applica un bonus del 15% sullo stipendio base. Altrimenti, nessun bonus.

Un approccio ingenuo: prima si fa una colonna con CERCA.X per recuperare il livello, poi si fa un’altra colonna con SE che legge quella colonna. Risultato: due colonne, doppio lavoro, doppia possibilità di sbagliare.

L’approccio corretto: si usa CERCA.X direttamente come argomento del SE. La funzione SE valuta il risultato di CERCA.X senza che voi abbiate bisogno di vederlo in una cella separata.

Formula · SE(CERCA.X(...))
SE(
CERCA.X(A2; TabellaHR[ID]; TabellaHR[Livello])= "Senior";
B2 * 1.15;
B2
)
→ Se il livello trovato è "Senior" → stipendio + 15%. Altrimenti → stipendio invariato.

La formula si aggiorna automaticamente ogni volta che la tabella HR viene modificata. Nessun copia-incolla manuale. Nessuna colonna di servizio. Nessun martedì pomeriggio dedicato ad aggiornare le cose a mano.

Pro Tip

Questo schema — SE(CERCA.X(...)="valore"; azione_se_vero; azione_se_falso) — è uno dei pattern più utili di tutto Excel. Imparatelo a memoria. Usatelo ogni volta che dovete prendere una decisione basata su un dato che si trova in un’altra tabella.

Nidificazione 2 · Farmacia / Magazzino · SOMMA.SE + CERCA.VERT
Sommare le scorte di tutti i prodotti appartenenti a una categoria

Il magazziniere della farmacia ha una tabella scorte con codice prodotto, quantità, e categoria. Deve creare un riepilogo che mostri la quantità totale per ogni categoria. Ma non ha la categoria direttamente nella colonna giusta — deve recuperarla dalla tabella prodotti per ogni articolo.

La soluzione è usare CERCA.VERT come argomento del criterio in SOMMA.SE, oppure — ancora più elegante — costruire una colonna ausiliaria con CERCA.VERT e poi usarla come criterio. In questo caso mostriamo l’approccio a colonna ausiliaria, che è più leggibile e meno soggetto a errori.

Colonna ausiliaria · Recupero categoria
CERCA.VERT(A2; TabellaProdotti!A:C; 3; FALSO)
→ Recupera la categoria per ogni codice prodotto. Ora avete la categoria in colonna D.

Formula finale · SOMMA.SE sulla colonna con la categoria
SOMMA.SE(D:D; "Antidolorifici"; B:B)
→ Somma tutte le quantità (colonna B) dove la categoria (colonna D) è "Antidolorifici".

Risultato: un riepilogo dinamico per categoria che si aggiorna ogni volta che cambiano le scorte o la tabella prodotti. Il magazziniere non deve più calcolare niente a mano e può smettere di usare la calcolatrice del telefono tenendola nascosta sotto il bancone.

Nidificazione 3 · Help Desk · TESTO + CERCA.X
Costruire messaggi automatici con dati presi da una tabella

L’operatore dell’help desk deve generare automaticamente un testo riepilogativo per ogni ticket: “Ticket #1042 — Cliente: Mario Rossi — Priorità: Alta — Assegnato a: Giulia M.” L’ID ticket è in colonna A, tutti gli altri dati sono in una tabella separata.

La soluzione è usare più CERCA.X dentro una funzione CONCATENA (o direttamente con l’operatore &), costruendo la stringa pezzo per pezzo con i dati recuperati dalla tabella.

Formula · Costruzione messaggio con CERCA.X annidato
"Ticket #"&A2&" — Cliente: "&
CERCA.X(A2; Ticket[ID]; Ticket[Cliente]) &
" — Priorità: "&
CERCA.X(A2; Ticket[ID]; Ticket[Priorità]) &
" — Assegnato a: "&
CERCA.X(A2; Ticket[ID]; Ticket[Operatore])
→ Risultato: "Ticket #1042 — Cliente: Mario Rossi — Priorità: Alta — Assegnato a: Giulia M."

Pro Tip

Questo approccio è la base per costruire template di email, report automatici, o etichette stampabili direttamente da Excel. Ogni volta che vi trovate a copiare dati a mano per riempire un testo sempre uguale, pensate a questo pattern.

Altre funzioni dentro CERCA.X: 3 esempi

Ora andiamo dall’altra parte: funzioni annidate all’interno di CERCA.X. Invece di fornire un valore statico da cercare, fornite il risultato di una formula. Questo permette di fare ricerche dinamiche, trasformare i dati prima di cercarli, o calcolare il valore cercato al volo.

Dentro CERCA 1 · Magazzino / Logistica · CERCA.X con GIORNI.LAVORATIVI.TOT
Calcolare la data di consegna prevista in base al lead time del fornitore

Il responsabile logistico ha una tabella fornitori con ID fornitore e giorni di lead time. Per ogni ordine vuole calcolare la data di consegna prevista: data ordine + lead time del fornitore, saltando weekend e festivi. Il tutto in una cella sola, senza colonne di appoggio.

La soluzione: si annida CERCA.X come argomento di GIORNO.LAVORATIVO, che calcola una data lavorativa futura aggiungendo un numero di giorni lavorativi a una data di partenza. I giorni vengono presi dinamicamente dalla tabella fornitori tramite CERCA.X.

Formula · GIORNO.LAVORATIVO(data; CERCA.X(fornitore))
GIORNO.LAVORATIVO(
B2;← data dell'ordine
CERCA.X(A2; Fornitori[ID]; Fornitori[LeadDays]; 0)
)
→ Aggiunge i giorni lavorativi di lead time del fornitore alla data ordine. Se il fornitore non esiste: 0 giorni aggiunti.

Il parametro se_non_trovato = 0 di CERCA.X fa sì che i fornitori nuovi, non ancora in tabella, non generino errori: semplicemente la data di consegna coincide con la data dell’ordine, segnalando implicitamente che mancano dati.

Pro Tip

GIORNO.LAVORATIVO accetta un terzo parametro opzionale: l’elenco dei giorni festivi. Se avete una colonna con le festività nazionali (Natale, Ferragosto, ecc.), inseritela lì e la funzione le salterà automaticamente nei calcoli.

Dentro CERCA 2 · Ufficio Commerciale · CERCA.X con MAIUSC e ANNULLA.SPAZI
Cercare un cliente anche quando il nome è stato digitato male

Il commerciale riceve email con nomi di aziende cliente scritti in modi diversi: a volte tutto maiuscolo, a volte con spazi in più, a volte con maiuscole miste. La tabella CRM tiene i nomi in maiuscolo senza spazi extra. La ricerca diretta fallisce perché "mario srl" ≠ "MARIO SRL".

La soluzione è normalizzare il valore cercato prima di passarlo a CERCA.X, usando MAIUSC per convertire tutto in maiuscolo e ANNULLA.SPAZI per eliminare gli spazi superflui. Si annidano entrambe le funzioni come primo argomento di CERCA.X.

Formula · CERCA.X(MAIUSC(ANNULLA.SPAZI(...)))CERCA.X(
MAIUSC(ANNULLA.SPAZI(A2));
CRM[NomeCliente];
CRM[ResponsabileCommerciale];
"Cliente non trovato nel CRM"
)
→ Normalizza il testo in A2 prima di cercare. " mario srl " diventa "MARIO SRL" e viene trovato.

Attenzione

Questo funziona solo se anche i dati nella colonna di ricerca della tabella CRM sono in maiuscolo senza spazi. Se i dati sono disomogenei anche lì, dovete applicare la stessa normalizzazione anche alla colonna di ricerca — e a quel punto conviene pulire i dati alla fonte, non fare acrobazia con le formule.

Dentro CERCA 3 · Scuola · CERCA.X con OGGI e confronto date
Trovare automaticamente il docente di turno in base alla data odierna

La segreteria ha una tabella con le date dei turni di sorveglianza e il nome del docente assegnato per ciascuna data. Ogni mattina qualcuno apre il file e vuole sapere subito chi è di turno oggi, senza cercare manualmente nella tabella.

La soluzione è usare la funzione OGGI() come valore da cercare all’interno di CERCA.X. OGGI() restituisce sempre la data corrente, quindi la formula trova automaticamente il docente di turno per il giorno in cui viene aperto il file. Zero interazione manuale.

Formula · CERCA.X(OGGI(); ...)
CERCA.X(
OGGI();
Turni[Data];
Turni[DocenteDiTurno];
"Nessun turno programmato per oggi"
)
→ Ogni volta che il file viene aperto, mostra automaticamente il docente di turno per oggi.

Il parametro se_non_trovato gestisce i weekend e i giorni non programmati senza generare errori — fondamentale se il file viene aperto anche di sabato dal vicedirettore ansioso.

Pro Tip

Questo stesso schema — CERCA.X(OGGI(); ...) — funziona per qualsiasi tabella con date: orari di apertura stagionali, prezzi che cambiano per periodo, disponibilità di risorse per data. È uno dei pattern più sottovalutati e più utili di Excel.

Riepilogo: cosa portarsi a casa

Se doveste spiegare tutto questo a un collega in tre minuti davanti alla macchinetta del caffè, ecco cosa direste:

CERCA.VERT è stabile, universale, e funziona su qualsiasi versione di Excel. Ha due grandi limiti: la colonna di ricerca deve essere la prima, e non gestisce gli errori nativamente. Va benissimo per tabelle semplici e file condivisi con persone che usano Excel vecchio.

CERCA.X è la versione moderna e priva di quei limiti. Cerca in qualsiasi colonna, gestisce i valori non trovati con il parametro se_non_trovato, può cercare dall’ultima riga, e si legge molto più facilmente. Usatelo ogni volta che il vostro Excel lo supporta (Microsoft 365 e Excel 2021 in poi).

Nidificazione: entrambe le funzioni diventano molto più potenti quando le combinate con SE, SOMMA.SE, TESTO, CONCATENA come funzioni esterne, oppure con OGGI, MAIUSC, ANNULLA.SPAZI, GIORNO.LAVORATIVO come argomenti interni. Imparate i pattern, non le singole formule.

E la prossima volta che vedete #N/D, non prendete a calci il computer. Controllate prima se c’è uno spazio invisibile. Quasi sempre è quello. Quasi sempre.

Pro Tip Finale

Selezionate una cella con il valore non trovato, premete F2 per entrare in modifica, e guardate bene i bordi del testo: se il cursore parte un po’ più a destra del primo carattere, c’è uno spazio davanti. Usate ANNULLA.SPAZI e vivete felici.

VLookup and XLookup – The definitive guide

Everything you ever wanted to know, explained in plain human terms, with real examples, and without making you cry in front of an Excel spreadsheet.

Every year, millions of people open Excel, stare at two separate tables, and think: “there must be a way to connect these things without copying them one by one.” There is. It’s called VLOOKUP. And for a few years now there’s also XLOOKUP, which does the same thing but without the embarrassing flaws. This is the guide nobody ever made you read before leaving you alone with a #N/A error and a ruined Tuesday afternoon.

VLOOKUP: the classic that never goes out of style (but throws tantrums)

VLOOKUP stands for Vertical Lookup. In practice: you give it a value, it goes and finds it in a column of a table, and returns the corresponding value from another column in the same row. Like a waiter who looks up your name on the reservation list and tells you which table you’re sitting at.

Simple in theory. In practice, as we’ll see, that waiter sometimes says #N/A and disappears into the kitchen.

The full syntax

VLOOKUP(lookup_value; table_array; col_index_num; [range_lookup])

Parameter	What it does	Example
lookup_value – required	The value you want to look up. Can be text, a number, or a cell reference.	`A2` (the cell containing the employee code)
table_array – required	The table to search in. Important: the lookup column MUST be the first column (leftmost) of this selection.	`B2:E500` or `HRTable[#All]`
col_index_num – required	The column number within the table from which to return the result. If the table has 4 columns and you want the third, type 3.	`3` (returns the value from the 3rd column)
range_lookup – optional	`FALSE` = exact match (almost always what you want). `TRUE` = approximate match (useful for brackets, e.g. taxes, score ranges). Default: `TRUE`, so always type FALSE if you’re not sure.	`FALSE`

Classic trap

The range_lookup parameter defaults to TRUE. This means that if you forget it, Excel will perform an approximate match. On unsorted data, this produces wrong results without warning you. No error. Silently. Like a colleague who misunderstood the instructions but won’t admit it.

Basic VLOOKUP example

VLOOKUP(A2; SalaryTable; 3; FALSE)
→ Looks up the code in A2 in the first column of SalaryTable, returns the value from the 3rd column

Pro Tip

Always use structured table references (Table1[Column]) instead of hard-coded ranges (B2:E500). If you add rows to the table, the formula updates automatically. If you use a hard-coded range, you’ll have to remember to update it manually — and you will definitely forget at the worst possible moment.

XLOOKUP: the younger sibling who turned out better

XLOOKUP arrived in 2019 with Microsoft 365 and fixed virtually all of VLOOKUP’s structural problems. It’s not just “a newer VLOOKUP” — it’s a completely redesigned function that works differently, more flexibly, and more intuitively.

If VLOOKUP is the waiter who searches the list from top to bottom and can only look to the right, XLOOKUP is the maître d’ who can look anywhere, tell you if you’re not on the list without making a scene, and even search starting from the end.

The full syntax

XLOOKUP(lookup_value; lookup_array; return_array; [if_not_found]; [match_mode]; [search_mode])

Parameter	What it does	Example
lookup_value – required	The value you want to find. Identical to VLOOKUP.	`A2`
lookup_array – required	The column (or row) to search in. Doesn’t have to be the first one. Can be any column in the table.	`ClientsTable[ID]`
return_array – required	The column (or row) to return the result from. Completely separate from the lookup column. Can be to the left, to the right, anywhere.	`ClientsTable[ClientName]`
if_not_found – optional	What to return if the value is not found. If omitted, returns #N/A. With this parameter, you can return custom text, zero, or anything else.	`"Not found"` or `0`
match_mode – optional	`0` = exact match (default). `-1` = exact or next smaller. `1` = exact or next larger. `2` = with wildcard characters (*, ?).	`0`
search_mode – optional	`1` = first to last row (default). `-1` = last to first (useful if you want the most recent occurrence). `2` or `-2` = binary search (on sorted data, much faster).	`-1` (to find the most recent row)

Pro Tip

The if_not_found parameter alone is worth switching to XLOOKUP. The old IFERROR(VLOOKUP(...); "Not found") is verbose and repeats the formula twice. With XLOOKUP you write it once, clean, readable — and your colleagues will look at you with respect.

XLOOKUP(A2; ClientsTable[ID]; ClientsTable[ClientName]; "Client not registered")
→ Looks up A2 in the ID column, returns the ClientName. If not found: "Client not registered"

When to use one and when to use the other: 4 real examples

The short answer: always use XLOOKUP if your Excel supports it. But life is complicated, colleagues use older versions of Office, and sometimes there are legitimate reasons why VLOOKUP is still the right choice. Here they are.

VLOOKUP · Limitations

The lookup column must be the first one (leftmost)
References columns by number, not by name
No native error handling
Cannot search right to left
Cannot find the last occurrence

XLOOKUP · Advantages

Search in any column, return from any column
Uses column names, not arbitrary numbers
Built-in error handling with if_not_found
Searches in both directions
Finds the last occurrence with search mode -1

Example 1 · Library · VLOOKUP wins
File shared with colleagues on Excel 2016

The librarian has a catalogue with 8,000 books: ISBN code in column A, title in B, author in C, section in D. She needs to create a file that her part-time colleague — who uses Excel 2016 on the desktop computer in the basement — can open and use without errors.

XLOOKUP doesn’t exist in Excel 2016. If the librarian uses XLOOKUP, the colleague sees nothing but a #NAME? error on every cell. A potential diplomatic incident between floors. VLOOKUP wins here, full stop.

Solution · VLOOKUP (compatibility)
VLOOKUP(A2; Catalogue!A:D; 2; FALSE)
→ Looks up the ISBN in A2, returns the title (column 2). Works on any version of Excel.

Pro Tip

If you’re not sure which version of Excel your colleagues are using, go to File → Save As and check for a compatibility warning. Or just ask them directly. It’s socially acceptable.

Example 2 · Medical Practice · XLOOKUP wins
The lookup column is not the first one

The administrator at a medical practice has a patient table: surname in column A, first name in B, tax ID in C, assigned doctor in D, date of last visit in E. She needs to look up patients by tax ID (column C) and return the doctor (column D).

With VLOOKUP, the lookup column must be the first one. The tax ID is in column C. So she would have to move the columns around — probably breaking other references — or use the classic INDEX+MATCH trick. Or simply use XLOOKUP, which doesn’t have this problem.

Solution · XLOOKUP (search on any column)
XLOOKUP(F2; PatientsTable[TaxID]; PatientsTable[AssignedDoctor]; "Patient not found")
→ Looks up the tax ID in F2 in the TaxID column, returns the doctor. No need to move columns.

Example 3 · School · XLOOKUP wins
Finding the last occurrence (the most recent grade)

The school office keeps a grade register with thousands of rows: every time a teacher enters a grade, they add a row. The same student has dozens of rows. The principal wants to know the last grade entered for each student — i.e. the most recent one, which is the lowest occurrence in the table.

VLOOKUP always finds the first occurrence from the top. There is no native way to make it find the last one. With XLOOKUP, just set the search mode to -1: it searches from the last row upwards, so it finds the most recent occurrence.

Solution · XLOOKUP (last occurrence)
XLOOKUP(A2; GradeRegister[StudentID]; GradeRegister[Grade]; "No grade"; 0; -1)
→ Mode -1: searches from the last row. Returns the student's most recent grade.

Pro Tip

The -1 mode in XLOOKUP is also useful in help desks and warehouses to find the latest transaction or the most recent status update on a ticket or order. Whenever you have time-based data with duplicates and want the latest entry, think XLOOKUP with mode -1.

Example 4 · Shop / Point of Sale · VLOOKUP wins
Simplified tables with a fixed structure

The store manager has a price table with product code in column A and price in column B. Two columns. He’s used it for years. The structure never changes. He sends it every week to three branches, all of which run different versions of Excel — including a tablet running Excel Mobile.

In this case VLOOKUP is perfectly adequate and universally compatible. There’s no column constraint to work around, no need for advanced error handling, no reverse search. Using XLOOKUP here adds nothing and risks breaking the file on older devices. Simplicity wins.

Solution · VLOOKUP (simplicity and compatibility)
VLOOKUP(A2; PriceList!A:B; 2; FALSE)
→ Looks up the product code, returns the price. Works everywhere, including Excel Mobile.

“The right function isn’t always the newest one. It’s the one that solves the problem without creating new ones.”

XLOOKUP and VLOOKUP inside other functions: 3 examples

So far we’ve used lookup functions on their own. But Excel becomes truly powerful when you start using them as ingredients inside larger formulas. The looked-up value becomes the input for a calculation, a condition, or another function. These three situations will almost certainly come up for you.

Nesting 1 · HR Office · IF + XLOOKUP
Conditional bonus calculation based on employee level

The HR department needs to calculate the end-of-year bonus. The rule: if the employee’s level (retrieved from the HR table) is “Senior”, a 15% bonus is applied to the base salary. Otherwise, no bonus.

A naive approach: first make a column with XLOOKUP to retrieve the level, then make another column with IF that reads that column. Result: two columns, double the work, double the chance of making a mistake.

The correct approach: use XLOOKUP directly as an argument of IF. The IF function evaluates the result of XLOOKUP without you needing to see it in a separate cell.

Formula · IF(XLOOKUP(...))
IF(
XLOOKUP(A2; HRTable[ID]; HRTable[Level])= "Senior";
B2 * 1.15;
B2
)
→ If the level found is "Senior" → salary + 15%. Otherwise → salary unchanged.

The formula updates automatically every time the HR table is modified. No manual copy-pasting. No helper columns. No Tuesday afternoon spent updating things by hand.

Pro Tip

This pattern — IF(XLOOKUP(...)="value"; action_if_true; action_if_false) — is one of the most useful patterns in all of Excel. Learn it by heart. Use it every time you need to make a decision based on data that lives in another table.

Nesting 2 · Pharmacy / Warehouse · SUMIF + VLOOKUP
Summing stock for all products belonging to a category

The pharmacy warehouse manager has a stock table with product code, quantity, and category. He needs to create a summary showing the total quantity for each category. But he doesn’t have the category directly in the right column — he needs to retrieve it from the product table for each item.

The solution is to use VLOOKUP as the criteria argument in SUMIF, or — even more elegantly — build a helper column with VLOOKUP and then use it as the criteria. Here we show the helper column approach, which is more readable and less error-prone.

Helper column · Retrieving the category
VLOOKUP(A2; ProductTable!A:C; 3; FALSE)
→ Retrieves the category for each product code. You now have the category in column D.

Final formula · SUMIF on the category column
SUMIF(D:D; "Painkillers"; B:B)
→ Sums all quantities (column B) where the category (column D) is "Painkillers".

Result: a dynamic category summary that updates every time the stock or product table changes. The warehouse manager no longer has to calculate anything by hand and can stop using the phone calculator he keeps hidden under the counter.

Nesting 3 · Help Desk · TEXT + XLOOKUP
Building automatic messages with data pulled from a table

The help desk operator needs to automatically generate a summary text for each ticket: “Ticket #1042 — Client: John Smith — Priority: High — Assigned to: Julia M.” The ticket ID is in column A, all other data is in a separate table.

The solution is to use multiple XLOOKUPs inside a CONCATENATE function (or directly with the & operator), building the string piece by piece with data retrieved from the table.

Formula · Building a message with nested XLOOKUP
"Ticket #"&A2&" — Client: "&
XLOOKUP(A2; Tickets[ID]; Tickets[Client]) &
" — Priority: "&
XLOOKUP(A2; Tickets[ID]; Tickets[Priority]) &
" — Assigned to: "&
XLOOKUP(A2; Tickets[ID]; Tickets[Operator])
→ Result: "Ticket #1042 — Client: John Smith — Priority: High — Assigned to: Julia M."

Pro Tip

This approach is the foundation for building email templates, automated reports, or printable labels directly from Excel. Every time you find yourself copying data by hand to fill in the same text over and over, think of this pattern.

Other functions inside XLOOKUP: 3 examples

Now let’s go the other way: functions nested inside XLOOKUP. Instead of providing a static value to look up, you provide the result of a formula. This allows you to perform dynamic lookups, transform data before searching, or calculate the lookup value on the fly.

Inside LOOKUP 1 · Warehouse / Logistics · XLOOKUP with NETWORKDAYS
Calculating the expected delivery date based on supplier lead time

The logistics manager has a supplier table with supplier ID and lead time in days. For each order he wants to calculate the expected delivery date: order date + supplier lead time, skipping weekends and public holidays. All in a single cell, with no helper columns.

The solution: nest XLOOKUP as an argument of WORKDAY, which calculates a future working date by adding a number of working days to a start date. The days are pulled dynamically from the supplier table via XLOOKUP.

Formula · WORKDAY(date; XLOOKUP(supplier))
WORKDAY(
B2; ← order date
XLOOKUP(A2; Suppliers[ID]; Suppliers[LeadDays]; 0)
)
→ Adds the supplier's lead time working days to the order date. If the supplier doesn't exist: 0 days added.

The if_not_found = 0 parameter of XLOOKUP ensures that new suppliers not yet in the table don’t generate errors — the delivery date simply matches the order date, implicitly signalling that data is missing.

Pro Tip

WORKDAY accepts an optional third parameter: a list of public holidays. If you have a column with national holidays (Christmas, bank holidays, etc.), add it there and the function will automatically skip them in its calculations.

Inside LOOKUP 2 · Sales Office · XLOOKUP with UPPER and TRIM
Finding a client even when the name has been typed incorrectly

The sales rep receives emails with client company names written in different ways: sometimes all caps, sometimes with extra spaces, sometimes with mixed capitalisation. The CRM table stores names in uppercase with no extra spaces. A direct lookup fails because "mario srl" ≠ "MARIO SRL".

The solution is to normalise the lookup value before passing it to XLOOKUP, using UPPER to convert everything to uppercase and TRIM to remove extra spaces. Both functions are nested as the first argument of XLOOKUP.

Formula · XLOOKUP(UPPER(TRIM(...)))
XLOOKUP(
UPPER(TRIM(A2));
CRM[ClientName];
CRM[SalesRep];
"Client not found in CRM"
)
→ Normalises the text in A2 before searching. " mario srl " becomes "MARIO SRL" and is found.

Watch out

This only works if the data in the lookup column of the CRM table is also in uppercase with no extra spaces. If the data there is inconsistent too, you’ll need to apply the same normalisation to the lookup column as well — and at that point it’s better to clean the data at the source rather than doing acrobatics with formulas.

Inside LOOKUP 3 · School · XLOOKUP with TODAY and date comparison
Automatically finding the teacher on duty based on today’s date

The school office has a table with supervision duty dates and the name of the teacher assigned to each date. Every morning someone opens the file and wants to immediately know who is on duty today, without manually searching through the table.

The solution is to use the TODAY() function as the lookup value inside XLOOKUP. TODAY() always returns the current date, so the formula automatically finds the teacher on duty for the day the file is opened. Zero manual interaction.

Formula · XLOOKUP(TODAY(); ...)
XLOOKUP(
TODAY();
Duties[Date];
Duties[TeacherOnDuty];
"No duty scheduled for today"
)
→ Every time the file is opened, it automatically shows the teacher on duty for today.

The if_not_found parameter handles weekends and unscheduled days without generating errors — essential if the file gets opened on a Saturday by an anxious deputy head.

Pro Tip

This same pattern — XLOOKUP(TODAY(); ...) — works for any table with dates: seasonal opening hours, prices that change by period, resource availability by date. It’s one of the most underrated and most useful patterns in Excel.

Summary: what to take away

If you had to explain all of this to a colleague in three minutes by the coffee machine, here’s what you’d say:

VLOOKUP is stable, universal, and works on any version of Excel. It has two major limitations: the lookup column must be the first one, and it doesn’t handle errors natively. It’s perfectly fine for simple tables and files shared with people using older versions of Excel.

XLOOKUP is the modern version without those limitations. It searches in any column, handles missing values with the if_not_found parameter, can search from the last row, and is much easier to read. Use it whenever your Excel supports it (Microsoft 365 and Excel 2021 onwards).

Nesting: both functions become far more powerful when you combine them with IF, SUMIF, TEXT, CONCATENATE as outer functions, or with TODAY, UPPER, TRIM, WORKDAY as inner arguments. Learn the patterns, not the individual formulas.

And the next time you see #N/A, don’t kick the computer. First check whether there’s an invisible space. It almost always is. Almost always.

Final Pro Tip

Select a cell with the value that isn’t being found, press F2 to enter edit mode, and look carefully at the edges of the text: if the cursor starts slightly to the right of the first character, there’s a leading space. Use TRIM and live happily ever after.