Most offices have a file that someone updates by hand every week. Prices are looked up one by one in the price list, email addresses are copied into a message one at a time, duplicate clients are spotted by eye. None of it is hard, but it takes the afternoon, and every manual step is another chance to copy the wrong row.
Excel has a formula for most of these jobs. Here are five, each with a small example, what it returns and the mistake people make with it most often. The sample data is made up.
Before you start: version, names and separator
- XLOOKUP, UNIQUE and FILTER are in Excel 2021, Excel 2024, Microsoft 365 and Excel for the web. Excel 2019 and older don't have them; the cell shows
#NAME?. - TEXTJOIN is there from Excel 2019, IFERROR from Excel 2007.
- All five also work in Google Sheets.
- In a Russian or other localised Excel the function names are translated: XLOOKUP is ПРОСМОТРX, IFERROR is ЕСЛИОШИБКА. Type the first few letters and pick the function from the list Excel offers.
- The examples separate arguments with a semicolon, which is what Excel expects under the regional settings most computers in Moldova use. If yours expects a comma, Excel will say there is a problem with the formula; swap the semicolons for commas.
XLOOKUP: the price by product code, from another sheet
An order lists product codes and the prices are on another sheet, so someone finds each code and copies its price across.
The sheet "Produse" has the code in column A, the name in B and the price in lei in C:
- P-101, A4 paper (500 sheets), 85
- P-102, laser toner, 640
- P-103, ring binder, 32
On the order sheet the codes start in A2. In B2:
=XLOOKUP(A2; Produse!A:A; Produse!C:C; "lipsă")
For P-102 the cell shows 640. For a code that isn't in the price list, say P-150, it shows "lipsă" (Romanian for "missing"; write any text you like) instead of #N/A. Copy the formula down the column and every row gets its price.
Compared with VLOOKUP, there are no columns to count and the price column can sit anywhere, even to the left of the code. XLOOKUP looks for an exact match unless told otherwise.
Common mistake. A code you can see in the price list still comes back as "lipsă". Usually one of the two has a space at the end, or the code is a number in one sheet and text in the other. =LEN(A2) shows it: if the length is more than the characters you see, there are extra spaces, and TRIM removes them.
TEXTJOIN: a whole column in one cell
A message has to go to everyone on a list, and the addresses sit in a column. Copying them into the To field one by one is slow, and one always gets skipped.
Column B, from row 2:
- ana@exemplu.md
- office@firma-a.md
- (empty)
- contabil@firma-b.md
=TEXTJOIN(", "; TRUE; B2:B50)
You get one cell with ana@exemplu.md, office@firma-a.md, contabil@firma-b.md, ready to paste into the recipient field. The first argument is what goes between the values, here a comma and a space; TRUE tells it to skip empty cells. If the message goes to clients, paste the list into Bcc so they don't see each other's addresses.
Common mistake. With FALSE instead of TRUE, every empty cell leaves a double comma, and some mail programs refuse the whole list. A cell also holds at most 32,767 characters; above that, TEXTJOIN returns #VALUE!.
UNIQUE: the client list without duplicates
The sales log has one row per invoice, so the same client turns up ten times. To get the list of clients, someone copies the column and deletes the repeats, then does it again next month.
Column C, rows 2 to 500:
- Alfa SRL
- Beta Grup
- Alfa SRL
- Gamma Trade
- Beta Grup
=UNIQUE(C2:C500)
You get Alfa SRL, Beta Grup, Gamma Trade, each once, in the order they first appear. You type the formula in one cell and the results fill the cells below it; Excel calls this spilling. Add a new client inside the range and the list updates itself. For alphabetical order, wrap it: =SORT(UNIQUE(C2:C500)).
Common mistake. #SPILL!. The formula needs empty cells below it, and a single value in the way blocks the whole result. Clear those cells or move the formula. Two smaller ones: UNIQUE does not fix spelling, so "Alfa SRL" and "SRL Alfa" stay two clients, and empty rows in the range show up as a 0 at the end of the list.
FILTER: only the invoices over 1,000 lei
The director wants to see the large invoices only. Someone sets a filter, copies the visible rows to a new sheet, and repeats it whenever new invoices arrive.
Invoices in columns A to D (number, date, client, amount in lei):
- F-0145, 03.11, Alfa SRL, 850
- F-0146, 04.11, Beta Grup, 2400
- F-0147, 05.11, Gamma Trade, 1150
- F-0148, 06.11, Alfa SRL, 600
=FILTER(A2:D500; D2:D500>1000)
You get the rows for F-0146 and F-0147, all four columns, starting from the cell with the formula. A new invoice over 1,000 lei shows up in the result on its own. The condition can be any comparison: D2:D500>=5000 or C2:C500="Alfa SRL".
Common mistake. When no invoice matches, FILTER returns #CALC!. A third argument says what to show instead: =FILTER(A2:D500; D2:D500>1000; "none"). Also keep the condition the same height as the table: A2:D500 with D2:D400 gives an error.
IFERROR: a report without #N/A and #DIV/0!
The report for the director has a column of average prices, and wherever the quantity is zero or a code wasn't found, the cell shows #DIV/0! or #N/A. Someone clears them by hand before printing.
Sales in lei in column B, units sold in column C:
- 12000, 40
- 3500, 0
- 7200, 30
=IFERROR(B2/C2; 0)
The first row gives 300, the second 0 instead of #DIV/0!, the third 240. The second argument is what appears instead of the error: 0, an empty cell ("") or a short word.
Common mistake. IFERROR hides every error, including the ones you need to see. If the formula points at a deleted sheet (#REF!) or has a misspelled function name (#NAME?), the cell quietly shows 0 and the report looks fine. Check that a formula works before you wrap it. And think about what 0 does to the totals: an average over a column where errors became zeros comes out lower. "" is often the more honest choice, since AVERAGE skips it.
They combine
The formulas work inside each other. All invoice numbers over 1,000 lei in one cell:
=TEXTJOIN(", "; TRUE; FILTER(A2:A500; D2:D500>1000))
The sorted client list from earlier is the same idea: =SORT(UNIQUE(C2:C500)).
The short version
- A value from another table by code: XLOOKUP.
- A column in one cell: TEXTJOIN.
- A list without duplicates: UNIQUE.
- Only the rows that meet a condition: FILTER.
- No error codes in the report: IFERROR, used with care.
When the spreadsheet becomes the company's database
These formulas work well while one person looks after one file. It changes when the spreadsheet becomes the company's database: three people edit copies of the same file, someone emails "clients_final_2.xlsx", and nobody is sure which copy has this morning's payment. A formula can't help there, because the question is who changed what, and where.
That is when a CRM or a small automation pays for itself: clients, invoices and history in one place, everyone sees their own part, and the lists above become a saved filter or a report. We wrote about moving a client base from Excel into a CRM without losing anything along the way. If you'd like to talk about yours, see our CRM systems page or write to us.