--- title: "LOOKUP function in Excel: syntax and examples | PracticeExcel" description: "How to use LOOKUP in Excel and Google Sheets: grade bands, tiered rates and the last value in a column, with the sorting rule explained." canonical_url: "https://practiceexcelonline.com/functions/lookup" locale: en meta: "og:description": "How to use LOOKUP in Excel and Google Sheets: grade bands, tiered rates and the last value in a column, with the sorting rule explained." "og:image": "https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~TE9PS1VQIGZ1bmN0aW9uIGluIEV4Y2VsOiBzeW50YXggYW5kIGV4YW1wbGVz%2Cdescription_~SG93IHRvIHVzZSBMT09LVVAgaW4gRXhjZWwgYW5kIEdvb2dsZSBTaGVldHM6IGdyYWRlIGJhbmRzLCB0aWVyZWQgcmF0ZXMgYW5kIHRoZSBsYXN0IHZhbHVlIGluIGEgY29sdW1uLCB3aXRoIHRoZSBzb3J0aW5nIHJ1bGUgZXhwbGFpbmVkLg%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~TE9PS1VQKGxvb2t1cF92YWx1ZSwgbG9va3VwX3ZlY3RvciwgW3Jlc3VsdF92ZWN0b3JdKQ%2Cp_Ii9mdW5jdGlvbnMvbG9va3VwIg%2Cs_cCI5KP0uEgbl7RpA.png" "og:image:height": "600" "og:image:type": image/png "og:image:width": "1200" "og:title": "LOOKUP function in Excel: syntax and examples | PracticeExcel" "twitter:image": "https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~TE9PS1VQIGZ1bmN0aW9uIGluIEV4Y2VsOiBzeW50YXggYW5kIGV4YW1wbGVz%2Cdescription_~SG93IHRvIHVzZSBMT09LVVAgaW4gRXhjZWwgYW5kIEdvb2dsZSBTaGVldHM6IGdyYWRlIGJhbmRzLCB0aWVyZWQgcmF0ZXMgYW5kIHRoZSBsYXN0IHZhbHVlIGluIGEgY29sdW1uLCB3aXRoIHRoZSBzb3J0aW5nIHJ1bGUgZXhwbGFpbmVkLg%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~TE9PS1VQKGxvb2t1cF92YWx1ZSwgbG9va3VwX3ZlY3RvciwgW3Jlc3VsdF92ZWN0b3JdKQ%2Cp_Ii9mdW5jdGlvbnMvbG9va3VwIg%2Cs_cCI5KP0uEgbl7RpA.png" --- > Index of every page on this site: [llms.txt](https://practiceexcelonline.com/llms.txt) # **The LOOKUP function** Looks up a value in one row or column and returns the value in the same position from another. It always does an approximate match, so the lookup range must be sorted from smallest to largest. = LOOKUP( ## **Syntax** ``` LOOKUP(lookup_value, lookup_vector, [result_vector]) ```
**lookup_value **
The value to look for. LOOKUP finds the largest value that is less than or equal to it.
**lookup_vector **
The row or column to search, sorted A to Z or smallest to largest.
**result_vector **(optional)
The row or column to return from, the same size as lookup_vector. Left out, LOOKUP returns from lookup_vector.
## **Examples** D1`=LOOKUP(185,2A2:A6,3B2:B6)` | | A | B | C | D | | --- | --- | --- | --- | --- | | 1 | Lowest score | Grade | | **B** | | 2 | 0 | F | | | | 3 | 60 | D | | | | 4 | 70 | C | | | | 5 | 80 | B | | | | 6 | 90 | A | | | **In plain words:** Find 185 in 2the lowest scores in A2:A6, or the closest one below it, and return 3the grade next to it in B2:B6. D1=LOOKUP(60,A2:A6,B2:B6) | | A | B | C | D | | --- | --- | --- | --- | --- | | 1 | Lowest score | Grade | | **D** | | 2 | 0 | F | | | | 3 | 60 | D | | | | 4 | 70 | C | | | | 5 | 80 | B | | | | 6 | 90 | A | | | D1=LOOKUP(2,1/(B2:B6<>""),B2:B6) | | A | B | C | D | | --- | --- | --- | --- | --- | | 1 | Day | Sales | | **140** | | 2 | Mon | 120 | | | | 3 | Tue | 95 | | | | 4 | Wed | 140 | | | | 5 | Thu | | | | | 6 | Fri | | | | ## **Questions**
### Why does LOOKUP return the wrong value? The lookup range is not sorted from smallest to largest. LOOKUP always looks for the closest value below, so on unsorted data it stops in the wrong place. For an exact match, use XLOOKUP or VLOOKUP with FALSE.
### How do I get the last value in a column? =LOOKUP(2,1/(B2:B100<>""),B2:B100) returns the last filled cell. Filled cells become 1 and empty ones an error, and LOOKUP looking for 2 lands on the last 1.
## **Related functions** - [**VLOOKUP**](https://practiceexcelonline.com/functions/vlookup) - [**XLOOKUP**](https://practiceexcelonline.com/functions/xlookup) - [**MATCH**](https://practiceexcelonline.com/functions/match) - [**INDEX**](https://practiceexcelonline.com/functions/index) ```json {"@context":"https://schema.org","@graph":[{"@id":"https://practiceexcelonline.com/#identity","@type":"Organization","name":"PracticeExcel","url":"https://practiceexcelonline.com/","logo":{"@id":"https://practiceexcelonline.com/#logo"}},{"@id":"https://practiceexcelonline.com/#website","@type":"WebSite","inLanguage":"en-US","name":"PracticeExcel","url":"https://practiceexcelonline.com/","alternateName":["Practice Excel Online","PracticeExcelOnline.com"],"publisher":{"@id":"https://practiceexcelonline.com/#identity"},"workTranslation":{"@id":"https://excelopgaver.dk/#website"}},{"@id":"https://practiceexcelonline.com/functions/lookup#webpage","description":"How to use LOOKUP in Excel and Google Sheets: grade bands, tiered rates and the last value in a column, with the sorting rule explained.","name":"LOOKUP function in Excel: syntax and examples","url":"https://practiceexcelonline.com/functions/lookup","@type":["WebPage","FAQPage"],"breadcrumb":{"@id":"https://practiceexcelonline.com/functions/lookup#breadcrumb"},"isPartOf":{"@id":"https://practiceexcelonline.com/#website"},"mainEntity":[{"@id":"https://practiceexcelonline.com/functions/lookup#/schema/question/1"},{"@id":"https://practiceexcelonline.com/functions/lookup#/schema/question/2"}],"potentialAction":[{"@type":"ReadAction","target":["https://practiceexcelonline.com/functions/lookup"]}],"primaryImageOfPage":{"@id":"https://practiceexcelonline.com/#logo"}},{"@id":"https://practiceexcelonline.com/functions/lookup#breadcrumb","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","name":"Home","item":"https://practiceexcelonline.com","position":1},{"@type":"ListItem","name":"Excel functions","item":"https://practiceexcelonline.com/functions","position":2},{"@type":"ListItem","name":"LOOKUP function in Excel: syntax and examples","position":3}]},{"@id":"https://practiceexcelonline.com/functions/lookup#article","description":"How to use LOOKUP in Excel and Google Sheets: grade bands, tiered rates and the last value in a column, with the sorting rule explained.","headline":"LOOKUP function in Excel: syntax and examples","inLanguage":"en-US","thumbnailUrl":"https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~TE9PS1VQIGZ1bmN0aW9uIGluIEV4Y2VsOiBzeW50YXggYW5kIGV4YW1wbGVz%2Cdescription_~SG93IHRvIHVzZSBMT09LVVAgaW4gRXhjZWwgYW5kIEdvb2dsZSBTaGVldHM6IGdyYWRlIGJhbmRzLCB0aWVyZWQgcmF0ZXMgYW5kIHRoZSBsYXN0IHZhbHVlIGluIGEgY29sdW1uLCB3aXRoIHRoZSBzb3J0aW5nIHJ1bGUgZXhwbGFpbmVkLg%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~TE9PS1VQKGxvb2t1cF92YWx1ZSwgbG9va3VwX3ZlY3RvciwgW3Jlc3VsdF92ZWN0b3JdKQ%2Cp_Ii9mdW5jdGlvbnMvbG9va3VwIg%2Cs_cCI5KP0uEgbl7RpA.png","@type":["Article","TechArticle"],"author":{"@id":"https://practiceexcelonline.com/#identity"},"image":{"@id":"https://practiceexcelonline.com/#/schema/image/1"},"isPartOf":{"@id":"https://practiceexcelonline.com/functions/lookup#webpage"},"mainEntityOfPage":{"@id":"https://practiceexcelonline.com/functions/lookup#webpage"},"publisher":{"@id":"https://practiceexcelonline.com/#identity"}},{"@id":"https://practiceexcelonline.com/functions/lookup#/schema/question/1","@type":"Question","inLanguage":"en-US","name":"Why does LOOKUP return the wrong value?","acceptedAnswer":{"@type":"Answer","text":"The lookup range is not sorted from smallest to largest. LOOKUP always looks for the closest value below, so on unsorted data it stops in the wrong place. For an exact match, use XLOOKUP or VLOOKUP with FALSE."}},{"@id":"https://practiceexcelonline.com/functions/lookup#/schema/question/2","@type":"Question","inLanguage":"en-US","name":"How do I get the last value in a column?","acceptedAnswer":{"@type":"Answer","text":"=LOOKUP(2,1/(B2:B100\u003C>\"\"),B2:B100) returns the last filled cell. Filled cells become 1 and empty ones an error, and LOOKUP looking for 2 lands on the last 1."}},{"@id":"https://practiceexcelonline.com/#logo","@type":"ImageObject","caption":"PracticeExcel","contentUrl":"https://practiceexcelonline.com/brand/practiceexcel/icon-512.png","inLanguage":"en-US","url":"https://practiceexcelonline.com/brand/practiceexcel/icon-512.png"},{"@id":"https://practiceexcelonline.com/#/schema/image/1","@type":"ImageObject","contentUrl":"https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~TE9PS1VQIGZ1bmN0aW9uIGluIEV4Y2VsOiBzeW50YXggYW5kIGV4YW1wbGVz%2Cdescription_~SG93IHRvIHVzZSBMT09LVVAgaW4gRXhjZWwgYW5kIEdvb2dsZSBTaGVldHM6IGdyYWRlIGJhbmRzLCB0aWVyZWQgcmF0ZXMgYW5kIHRoZSBsYXN0IHZhbHVlIGluIGEgY29sdW1uLCB3aXRoIHRoZSBzb3J0aW5nIHJ1bGUgZXhwbGFpbmVkLg%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~TE9PS1VQKGxvb2t1cF92YWx1ZSwgbG9va3VwX3ZlY3RvciwgW3Jlc3VsdF92ZWN0b3JdKQ%2Cp_Ii9mdW5jdGlvbnMvbG9va3VwIg%2Cs_cCI5KP0uEgbl7RpA.png","inLanguage":"en-US","url":"https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~TE9PS1VQIGZ1bmN0aW9uIGluIEV4Y2VsOiBzeW50YXggYW5kIGV4YW1wbGVz%2Cdescription_~SG93IHRvIHVzZSBMT09LVVAgaW4gRXhjZWwgYW5kIEdvb2dsZSBTaGVldHM6IGdyYWRlIGJhbmRzLCB0aWVyZWQgcmF0ZXMgYW5kIHRoZSBsYXN0IHZhbHVlIGluIGEgY29sdW1uLCB3aXRoIHRoZSBzb3J0aW5nIHJ1bGUgZXhwbGFpbmVkLg%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~TE9PS1VQKGxvb2t1cF92YWx1ZSwgbG9va3VwX3ZlY3RvciwgW3Jlc3VsdF92ZWN0b3JdKQ%2Cp_Ii9mdW5jdGlvbnMvbG9va3VwIg%2Cs_cCI5KP0uEgbl7RpA.png"}]} ``` ## Sitemap See the full [sitemap](https://practiceexcelonline.com/sitemap.md) for all pages.