Excel LOOKUP Function: Complete Guide With Examples

The LOOKUP function feels like a relic from an older version of Excel, and in some ways it is. Microsoft introduced it long before VLOOKUP became famous, and modern guides usually steer you toward newer functions instead. Yet LOOKUP still shows up in legacy spreadsheets, audit files, and certification exams every single year. If you cannot read it, edit it, or replace it cleanly, you will trip on workbooks that were built ten years ago and still run payroll today.

This guide walks through both forms of LOOKUP, the vector form and the array form, with practical examples you can paste into any worksheet. You will learn the exact syntax, the sorting rule that breaks more lookups than any other mistake, and the modern replacements that handle the same job without the gotchas. If you are studying for an Excel certification or office assessment, our Excel skills guide covers the broader function library you will need on test day.

LOOKUP has one job: scan a sorted range, find the largest value that is less than or equal to your lookup value, and return the matching item from a result range. That sounds simple, and for clean data it is. Real spreadsheets are rarely clean, which is why LOOKUP earned its reputation for silent wrong answers. The function returns a value almost no matter what, but that value is only correct when the lookup column is sorted in ascending order. Miss that requirement and Excel will hand you a confident, plausible, and completely wrong result.

Vector Form vs Array Form

LOOKUP comes in two flavours and they look almost identical at first glance. The difference matters because each one accepts arguments in a different order, and mixing them up is the fastest route to a broken formula.

The vector form takes three arguments: the value you want to find, a one-row or one-column range to search, and a separate one-row or one-column range to pull the result from. The two ranges must be the same size. This form is the safer choice because the lookup range and result range live in different columns, which makes the formula easier to read and to audit later.

The array form takes two arguments: the lookup value and a single rectangular range. Excel searches the first row or column of that range, depending on its shape, and returns a value from the last row or column. Wider ranges search left to right. Taller ranges search top to bottom. The array form survives in modern Excel only for backward compatibility, and Microsoft recommends switching to VLOOKUP, HLOOKUP, or XLOOKUP for any new work. Most legacy spreadsheets you will inherit still use it, so reading the syntax fluently matters even if you never write it again.

One more quirk separates LOOKUP from its newer relatives. LOOKUP does not have a fourth argument for exact match. It always performs an approximate match against sorted data. If your lookup column is not sorted ascending, LOOKUP will return whatever the binary search algorithm lands on, which is rarely the row you wanted.

How the Vector Form Works in Practice

Imagine a price list in cells A2 through A20 with product codes sorted alphabetically, and a matching list of prices in B2 through B20. You want to type a product code into D1 and see the price appear in E1. The formula in E1 reads =LOOKUP(D1, A2:A20, B2:B20). Excel scans the codes in column A, finds the closest match that is less than or equal to D1, and pulls the corresponding price from column B.

The function does not raise an error for a missing code. If you type a code that does not exist, LOOKUP returns the price of the previous code in sorted order. That is the behaviour that bites accounting teams every quarter. Someone fat-fingers a product number, the formula returns a number that looks reasonable, and the invoice goes out wrong. There is no warning, no #N/A, no clue that the lookup missed.

The sort requirement is strict. Even one out-of-order entry can throw off the binary search. If you inherit a spreadsheet and you are not sure the lookup column is sorted, the safest move is to copy the column, paste as values into a scratch sheet, sort it, and compare side by side. Any mismatch means the original is not sorted and any LOOKUP formula pointed at it is unreliable. If you also need to summarise the matched data, drop the results into a pivot table for instant grouping.

The Array Form and Why It Survives

The array form looks like this: =LOOKUP(D1, A2:B20). With a two-column range, Excel searches column A and returns the matching value from column B. With a five-column range, Excel still searches the first column and returns from the last. With a range that is wider than it is tall, Excel flips the logic and searches the first row instead of the first column, returning from the last row.

That automatic dimension switch is the source of most array-form bugs. A spreadsheet that worked perfectly in 2018 stops working in 2024 because someone added two extra columns to the right of the data, and now the range is wider than expected. The formula still calculates without error, but it pulls from a different column. Audit trails do not catch this because nothing visibly broke.

The array form persists because Lotus 1-2-3 used it, and Excel kept compatibility when it imported old Lotus files in the early 1990s. Forty years later, the syntax is still in the function library. Treat it like a fossil. Recognise it when you find it, document what it returns, and replace it with VLOOKUP or XLOOKUP the next time you touch the workbook.

The Sort Rule Explained

LOOKUP uses a binary search algorithm. Binary search assumes the data is sorted. The algorithm picks the middle entry, compares it to the target, and decides whether to look in the upper or lower half. It repeats the split until it lands on a match or runs out of data. The whole approach falls apart when the data is not sorted because the upper-or-lower decision sends the search down the wrong branch.

Sorting must be ascending. Text sorts A to Z. Numbers sort smallest to largest. Dates sort earliest to latest. Mixed types cause headaches because Excel treats numbers stored as text differently from real numbers. The classic trap is a product code column where some codes are numeric (1001, 1002) and others have leading zeros stored as text ("0099"). Excel sorts these in two separate groups and LOOKUP returns wrong answers across the boundary.

Case sensitivity does not exist in LOOKUP. The function treats APPLE, Apple, and apple as equivalent. If you need case-sensitive lookups, use INDEX with MATCH and the EXACT function, or switch to XLOOKUP with a custom match mode. For most accounting and inventory work this does not matter, but it bites hard in any system that uses case to distinguish records.

One sort trick that saves time on inherited spreadsheets is the COUNTIF audit. Drop a helper column with =IF(A2>=A1,1,0) next to the lookup vector and copy it down. Sum the helper column. The total must equal the row count minus one. Any gap means the data is unsorted and any LOOKUP against it cannot be trusted.

When to Replace LOOKUP With Something Newer

Three modern functions handle every LOOKUP use case more safely. VLOOKUP, HLOOKUP, and XLOOKUP all support exact-match mode, raise visible errors for missing values, and do not require sorted data. If you are starting a new workbook in any Excel version from 2007 onward, there is no good reason to reach for LOOKUP first.

VLOOKUP is the workhorse. The formula =VLOOKUP(D1, A2:B20, 2, FALSE) searches column A for an exact match and returns the value in column B. The FALSE argument forces exact matching and returns #N/A for missing values, which is exactly the protection LOOKUP lacks. If your lookup column is to the right of your result column, VLOOKUP cannot help, and you need INDEX with MATCH or XLOOKUP instead.

XLOOKUP arrived with Microsoft 365 and is the cleanest replacement. The formula =XLOOKUP(D1, A2:A20, B2:B20, "Not found") searches column A, returns from column B, and shows a friendly fallback if the value is missing. XLOOKUP also accepts search direction arguments, so you can search bottom-up for the most recent entry or use wildcards for partial matches. The trade-off is that XLOOKUP only exists in Microsoft 365 and Excel 2021. Anyone on Excel 2019 or earlier sees a #NAME? error.

For backward compatibility, INDEX with MATCH remains the universal answer. The formula =INDEX(B2:B20, MATCH(D1, A2:A20, 0)) works in every Excel version from 2003 onward, supports exact matching, and does not care about column order. It is wordier than XLOOKUP but more portable.

Troubleshooting Common LOOKUP Errors

The #N/A error appears when the lookup value is smaller than the smallest entry in the lookup vector. Binary search has nothing below the value, so it bails out. The fix is either to add a sentinel row at the top with a tiny value and a default result, or wrap the formula in IFNA: =IFNA(LOOKUP(D1, A2:A20, B2:B20), "Not in list").

The #REF! error appears when the lookup vector and result vector are different sizes. Excel cannot align the two ranges, so it refuses to calculate. Check that both ranges start at the same row and end at the same row. If you inserted a row in one column but not the other, the ranges have drifted apart.

The #VALUE! error appears when you point LOOKUP at a range that is not a single row or single column. The vector form will not accept a two-dimensional range as either the lookup vector or the result vector. Use the array form for two-dimensional data, or switch to INDEX with MATCH for full control over rows and columns.

Wrong answers without any error are the worst case and they always trace back to one of two causes. Either the lookup column is not sorted ascending, or the lookup value is missing and Excel returned the previous row instead. There is no way to distinguish a correct match from a previous-row fallback without comparing the input to the result manually. This is why so many audit teams ban LOOKUP outright and require VLOOKUP with FALSE or XLOOKUP for any production workbook.

LOOKUP in Interviews and Excel Certifications

Microsoft Office Specialist exams still ask about LOOKUP because the question separates candidates who memorised the function library from those who only know modern shortcuts. The exam usually presents a small dataset, asks you to write a formula that pulls a value from a sorted lookup column, and grades on syntax accuracy. Expect at least one question that hinges on the sort-order rule.

Job interviews at finance and analytics firms also lean on LOOKUP for a different reason. The interviewer wants to see whether you understand approximate-match logic. Most candidates panic and write VLOOKUP with FALSE, which is the safest answer but misses the point. The cleaner reply is to walk through both options, explain that LOOKUP requires sorted data, and offer XLOOKUP as the modern equivalent. Demonstrating that you know all three functions and when each one applies will score higher than reciting one syntax from memory.

If you are preparing for the Microsoft Excel Assessment test, practice all four lookup methods until you can write them without thinking. Start with VLOOKUP, then HLOOKUP, then INDEX/MATCH, then XLOOKUP, then circle back to LOOKUP for legacy questions. The functions feel similar at first but each one has a specific use case the others cannot match. Test prep workbooks usually layer the questions so that candidates who only know VLOOKUP get stuck on the third or fourth scenario.

Next Steps

Spend an hour practising both forms of LOOKUP on a sample dataset. Build a small price list, sort it, and write five different formulas: one for an exact match, one for an out-of-range value, one for the last entry trick, one that breaks because the data is unsorted, and one that wraps the result in IFNA. That hour pays back every time you inherit a workbook with old LOOKUP formulas you need to read or repair.

For the broader Excel function family, explore our coverage of VLOOKUP, INDEX/MATCH patterns, and Power Query for data preparation that happens before the lookup even starts. Each function has its place. Knowing which one to use when separates the analyst who finishes a task in five minutes from the one who fights the spreadsheet for an hour.

A final piece of context worth knowing. Spreadsheet auditors at the major accountancy firms keep a running list of functions they flag in any inherited model. LOOKUP sits near the top of that list because it returns plausible wrong answers without raising an error.

When a junior analyst takes over a workbook from a colleague who left the firm last quarter, the auditor will quietly replace every LOOKUP with VLOOKUP or XLOOKUP before signing off. That replacement work takes hours and adds nothing visible to the output, which is exactly why the original author should have used a safer function in the first place.

The same audit pressure shows up in regulated industries. Pharmaceutical validation, banking stress tests, and government tax submissions all require that calculations behave predictably under every input. A function that fails silently is a regulatory red flag. If you work in any field where spreadsheets contribute to compliance reports, treat LOOKUP as legacy code and migrate it whenever you touch the surrounding cells. The few extra seconds it takes to type VLOOKUP with FALSE will save you a frantic afternoon when an auditor flags a discrepancy six months later.