What is Excel LOOKUP function ? LOOKUP function is one of the lookup and reference functions in Microsoft office Excel that is used to return a value from a range (one row or one column) or from an array. When you need to look in a single row or column and find a value from the same position in a second row or…
Tag: LOOKUP function
Highlight approximate match lookup conditional formatting in Excel
This tutorial shows how to Highlight approximate match lookup conditional formatting in Excel using the example below; Formula =OR($B5=LOOKUP(width,widths),B$5=LOOKUP(height,heights)) Explanation To highlight rows and columns associated with an approximate match, you can use conditional formatting with a formula based on the LOOKUP function together with with a logical function like OR or AND. In the example shown, the formula used to…
Group numbers at uneven intervals in Excel
This tutorial shows how to Group numbers at uneven intervals in Excel using the example below; Formula =LOOKUP(value,intervals,groups) Explanation To group numbers into intervals of unequal size, you can use the LOOKUP function. In the example shown, the LOOKUP function is used to group people by age into at intervals of unequal size. The formula in D5 is: =LOOKUP(C5,age,group) Where…
Lookup value between two numbers in Excel
This tutorial shows how to Lookup value between two numbers in Excel using the example below; Formula =LOOKUP(B5,minimums,results) Explanation To lookup values between two values and return a corresponding result, you can use the LOOKUP function and a sorted table. In the example shown, the formula in C5 is: =LOOKUP(B5,mins,results) where “mins” is the named range E5:E9, and “results” is the named…
Lookup latest price in Excel
This tutorial shows how to Lookup latest price in Excel using the example below; Formula =LOOKUP(2,1/(item=”hat”),price) Explanation To lookup the most recent price for an item in a list, where latest items appear last, you can use a formula based on the LOOKUP function. In the example show, the formula in G7 is: =LOOKUP(2,1/(item=F7),price) where item is the named range…
Lookup last file version in Excel
This tutorial shows how to Lookup last file version in Excel using the example below; Formula =LOOKUP(2,1/(ISNUMBER(FIND(filename,range))),range) Explanation To lookup the latest file version in a list, you can use a formula based on the LOOKUP function together with the ISNUMBER and FIND functions. In the example shown, the formula in cell G7 is: =LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files) where “files” is the named range B5:B11. Context…
Get last match cell contains in Excel
This tutorial shows how to Get last match cell contains in Excel using the example below; Formula =LOOKUP(2,1/SEARCH(things,A1),things) Explanation To check a cell for one of several things, and return the last match found in the list, you can use a formula based on the LOOKUP and SEARCH functions. In the case of multiple matches found, the formula will return…
Average the last 3 numeric values in Excel
This tutorial shows how to work Average the last 3 numeric values in Excel using the example below; Formula {=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),{1,2,3}),ROW(data), data))} Explanation To average the last 3 numeric values in a range, you can use an array formula based on a combination of functions to feed the last n numeric values into the AVERAGE function. In the example shown, the formula in D6…
How to use Excel LOOKUP Function
This Excel tutorial explains how to use the LOOKUP function with syntax and examples. Excel LOOKUP function Description The Microsoft Excel LOOKUP function returns a value from a range (one row or one column) or from an array. The LOOKUP function is a built-in function in Excel that is categorized as a Lookup/Reference Function. It can be used as a worksheet function (WS)…
Two-column Lookup in Excel
This example teaches you how to perform a two-column lookup in Excel. See the example below. We want to look up the salary of James Clark, not James Smith, not James Anderson. 1. To join strings, use the & operator. 2. The MATCH function returns the position of a value in a given range. Insert the MATCH function shown below. 3. Finish by…