The VLOOKUP function is one of the most popular functions in Excel. This chapter contains many easy to follow VLOOKUP examples for Exact Match, Approximate Match, Right Lookup, First Match, Case-insensitive, Multiple Criteria, #N/A error and Multiple Lookup Tables. Exact Match Most of the time you are looking for an exact match when you use the VLOOKUP function in Excel. …
Learn all about Excel’s lookup & reference functions such as the VLOOKUP, HLOOKUP, CHOOSE, MATCH and INDEX function. Navigation: Formula Tab → Function Library Group → Lookup and Reference Vlookup VLOOKUP stands for Vertical lookup. The VLOOKUP function looks for a value in the leftmost column of a table, and then returns a value in the same row from another column …
What is INDEX function in Excel? INDEX function is one of Lookup and Reference functions in Microsoft Excel that returns a value or the reference to a value from within a table or range. There are two ways to use the INDEX function: If you want to return a reference to specified cells, Click to see Reference …
To dynamically sort text values in alphabetical order, you can use use a formula based on the COUNTIF function. Formula =COUNTIF(range,”<=”&A1) Explanation In the example shown, the formula in C5 is: =COUNTIF(countries,”<=”&B5) where “countries” is the named range B4:B13 How this formula works This formula uses the “greater than or equal to” operator with text, something you …
To get a random value from a table or list in Excel, you can use the INDEX function with help from the RANDBETWEEN and ROWS functions. Formula =INDEX(data,RANDBETWEEN(1,ROWS(data)),1) Note: this formula uses the named range “data” (B5:E104) for readability and convenience. If you don’t want to use a named range, substitute $B$5:$E$104 instead. Explanation In …
To retrieve the first match in two ranges of values, you can use a formula based on the INDEX, MATCH, and COUNTIF functions. Formula =INDEX(range2,MATCH(TRUE,COUNTIF(range1,range2)>0,0)) Explanation In the example shown, the formula in G5 is: =INDEX(range2,MATCH(TRUE,COUNTIF(range1,range2)>0,0)) where “range1” is the named range B5:B8, “range2” is the named range D5:D7. How this formula works In this example the named range …
This tutorials show examples one and two dynamic named ranges created. The first is created with the INDEX function together with the COUNTA function. Dynamic named ranges automatically expand and contract when data is added or removed. Formula =$A$1:INDEX($A:$A,lastrow) Explanation This page shows an example of a dynamic named range created with the INDEX function together with the COUNTA function. …
You can get the first row (i.e. the starting row number) in a range with a formula based on the ROW function. Formula =MIN(ROW(range)) Explanation In the example shown, the formula in cell F5 is: =MIN(ROW(data)) where data is a named range for B5:D10 How this formula works When given a single cell reference, the …
To get a full set of relative row numbers in a range, you can use an array formula based on the ROW function. See example below; Formula {=ROW(range)-ROW(range.firstcell)+1} Note: this is an array formula that must be entered with Control + Shift + Enter. If you’re entering this on the worksheet (and not inside another formula), make …
You can get the last column in a range with a formula based on the COLUMN function. See example below: Formula =MIN(COLUMN(range))+COLUMNS(range)-1 Note: When given a single cell reference, the COLUMN function returns the column number for that reference. However, when given a range that contains multiple columns, the COLUMN function will return an array …
You can get the last row in a range with a formula based on the ROW function. See example below; Formula =MIN(ROW(range))+ROWS(range)-1 Explanation In the example shown, the formula in cell F5 is: =MIN(ROW(data))+ROWS(data)-1 where data is a named range for B5:D10 How this formula works When given a single cell reference, the ROW function …
To calculate the average for the last N values n an Excel table (i.e. last 3 rows, last 5 rows, etc.) you can use the AVERAGE function together with the INDEX and ROWS functions. See example below: Formula =AVERAGE(INDEX(table[column],ROWS(table)-(N-1)):INDEX(table[column],ROWS(table))) Explanation In the example shown, the formula in F5 is: =AVERAGE(INDEX(Table1[Sales],ROWS(Table1)-(F4-1)):INDEX(Table1[Sales],ROWS(Table1))) How this formula works This …
To use COUNTIFS with a variable table column, you can use INDEX and MATCH to find and retrieve the column for COUNTIFS. See example below: Formula =COUNTIFS(INDEX(Table,0,MATCH(name,Table[#Headers],0)),criteria)) Explanation In the example shown, the formula in H5 is: =COUNTIFS(INDEX(Table1,0,MATCH(G5,Table1[#Headers],0)),”x”) How this formula works First, for context, it’s important to note that you can use COUNTIFS with …
To get the name of a column in an Excel Table from its numeric index, you can use the INDEX function with a structured reference. See example below: Formula =INDEX(Table[#Headers],index) Explanation In the example shown, the formula in I4 is: =INDEX(Table1[#Headers],H5) When the formula is copied down, it returns an name for each column, based …
This tutorial shows illustrates a Running total in Excel Table. To create a running total in an Excel Table, you can use the INDEX function set up with a structured reference. Formula =SUM(INDEX([column],1):[@column]) Explanation In the example shown, the formula in F5 is: =SUM(INDEX([Total],1):[@Total]) When copied down the column, this formula will return a running total …
To do a two-way lookup in an Excel Table, you can use the MATCH function with a structured reference and VLOOKUP. See example below: Recall that VLOOKUP depends on the lookup value being to the left of the value being retrieved in a table. Generally, this means the lookup value will be the first value in the table. …
To extract the word or text value that occurs most frequently in a range, you can use a formula based on several functions INDEX, MATCH, and MODE. Formula =INDEX(range,MODE(MATCH(range,range,0))) Explanation In the example shown, the formula in H5 is: =INDEX(B5:F5,MODE(MATCH(B5:F5,B5:F5,0))) Working from the inside out, the MATCH function matches the range against itself. That is, …
To find the most frequently occurring text in a range, based on criteria you supply, you can use an array formula based on several Excel functions MATCH, MODE, INDEX, and IF. Formula =INDEX(range1,MODE(IF(range2=criteria, MATCH(range1,range1,0)))) Note: this is an array formula and must be entered with control + shift + enter. Explanation In the example shown, the formula …
To generated a random number, weighted with a given probability, you can use a helper table together with a formula based on the RAND and MATCH functions. Formula =MATCH(RAND(),cumulative_probability) Explanation In the example shown, the formula in F5 is: =MATCH(RAND(),D$5:D$10) How this formula works This formula relies on the helper table visible in the range …
To find and replace multiple values with a formula, you can nest multiple SUBSTITUTE functions together, and feed in find/replace pairs from another table using the INDEX function. Formula =SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)), INDEX(find,2),INDEX(replace,2)) Explanation In the example shown, we are performing 4 separate find and replace operations. The formula in G5 is: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1), INDEX(replace,1)),INDEX(find,2),INDEX(replace,2)), INDEX(find,3),INDEX(replace,3)),INDEX(find,4),INDEX(replace,4)) where “find” is …
To list worksheets in an Excel workbook, you can use a 2-step approach: (1) define a named range called “sheetnames” with an old macro command and (2) use an INDEX formula to retrieve sheet names using the named range. Formula =GET.WORKBOOK(1)&T(NOW()) Note: because this formula relies on a macro command, you’ll need to save as a macro-enabled workbook …
To get the next scheduled event from a list of events with dates, you can use an array formula based on the MIN and TODAY functions to find the next date, and INDEX and MATCH to display the event on that date. Formula {=MIN(IF((range>=TODAY()),range))} Note: this is an array formula and must be entered with Control + …
This tutorial shows how to Categorize text with keywords in Excel using the example below; Formula {=INDEX(categories,MATCH(TRUE,ISNUMBER (SEARCH(keywords,text)),0))} Explanation To categorize text using keywords with a “contains” match, you can use the SEARCH function, with help from INDEX and MATCH. In the example shown, the formula in C5 is: {=INDEX(categories,MATCH(TRUE, ISNUMBER(SEARCH(keywords,B5)),0))} where “keywords” is the named …
This tutorial shows how to Extract all partial matches in Excel using the example below; Formula =IF(F5>ct,””,INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5))) Explanation To extract all matches based on a partial match, you can use use an array formula based on the INDEX and AGGREGATE functions, with support from ISNUMBER and SEARCH. In the example shown, the formula in G5 …
This tutorial shows how to calculate Index and match on multiple columns in Excel using the example below; Formula {=INDEX(range1,MATCH(1,MMULT(–(range2=critera), TRANSPOSE(COLUMN(range2)^0)),0))} Explanation To lookup a value by matching across multiple columns, you can use an array formula based on the MMULT, TRANSPOSE, COLUMN, and INDEX. In the example shown, the formula in H4 is: {=INDEX(groups,MATCH(1,MMULT(–(names=G4), TRANSPOSE(COLUMN(names)^0)),0))} where …
This tutorial shows how to Extract data with helper column in Excel using the example below; Formula =IF(rowcheck,INDEX(data,MATCH(rownum,helper,0),column),””) Explanation One way to extract data in Excel is to use INDEX and MATCH with a helper column that marks matching data. This avoids the complexity of a more advanced array formula. In the example shown, the …
This tutorial shows how to Join tables with INDEX and MATCH in Excel using the example below; Formula =INDEX(data,MATCH(lookup,ids,0),2) Explanation To join or merge tables that have a common id, you can use the INDEX and MATCH functions. In the example shown, the formula in E5 is: =INDEX(data,MATCH($C5,ids,0),2) where “data” is the named range H5:J8 and “ids” …
This tutorial shows how to calculate Extract multiple matches into separate rows in Excel using the example below; Explanation To extract multiple matches to separate cells, in separate rows, you can use an array formula based on INDEX and SMALL. In the example shown, the formula in E5 is: {=IFERROR(INDEX(names,SMALL (IF(groups=E$4,ROW(names)-MIN(ROW (names))+1),ROWS($E$5:E5))),””)} This is an …
This tutorial shows how to calculate INDEX and MATCH with multiple criteria in Excel using the example below; Formula {=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0))} Explanation To lookup values with INDEX and MATCH, using multiple criteria, you can use an array formula. In the example shown, the formula in H8 is: {=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0))} Note: this is an array formula, and must be …
This tutorial shows how to Find closest match in Excel using the example below; Formula {=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0))} Explanation To find the closest match with a lookup value and numeric data, you can use an array formula based the INDEX, MATCH, ABS and MIN functions. In the example shown, the formula in E5 is: {=INDEX(data,MATCH(MIN(ABS(data-E4)),ABS(data-E4),0))} where “data” …
This tutorial shows how to Lookup and sum column in Excel using the example below; Formula =SUM(INDEX(data,0,MATCH(val,header,0))) Explanation To lookup and return the sum of a column, you can use the a formula based on the INDEX, MATCH and SUM functions. In the example shown, the formula in I7 is: =SUM(INDEX(C5:F11,0,MATCH(I6,C4:F4,0))) How this formula works …
This tutorial shows how to Find longest string in column in Excel using the example below; Formula {=INDEX(range,MATCH(MAX(LEN(range)),LEN(range),0))} Explanation To find the longest string (name, word, etc.) in a column, you can use an array formula based on INDEX and MATCH, together with LEN and MAX. In the example shown, the formula in F6 is: …
This tutorial shows how to Lookup entire column in Excel using the example below; Formula =INDEX(data,0,MATCH(value,headers,0)) Explanation To lookup and retrieve an entire column, you can use a formula based on the INDEX and MATCH functions. In the example shown, the formula used to lookup all Q3 results is: =INDEX(C5:F8,0,MATCH(I5,C4:F4,0)) Note: this formula is embedded …
This tutorial shows how to Find longest string with criteria in Excel using the example below; Formula {=INDEX(range1,MATCH(MAX(LEN(range1)*(range2=criteria)),LEN(range1)*(range2=criteria),0))} Explanation To find the longest string in a range with criteria, you can use an array formula based on INDEX, MATCH, LEN and MAX. In the example shown, the formula in F6 is: {=INDEX(names,MATCH(MAX(LEN(names)*(class=F5)),LEN(names)*(class=F5),0))} Where “names” is …
This tutorial shows how to Lookup entire row in Excel using the example below; Formula =INDEX(data,MATCH(value,array,0),0) Explanation To lookup and retrieve an entire row, you use the INDEX and MATCH functions. In the example shown, the formula used to look up all values for the Central region is: =INDEX(C5:F8,MATCH(H5,B5:B8,0),0) How this formula works The gist: …
This tutorial shows how to calculate First match in range with wildcard in Excel using the example below; Formula =INDEX(range,MATCH(val&”*”,range,0)) Explanation To get the value of the first match in a range using a wildcard, you can use an INDEX and MATCH formula, configured for exact match. In the example shown, the formula in F5 …
This tutorial shows how to Lookup lowest value in Excel using the example below; Formula =INDEX(range,MATCH(MIN(vals),vals,0)) Explanation To lookup information associated with the lowest value in table, you can use a formula based on INDEX, MATCH, and MIN functions. In the example shown, a formula is used to identify the name of the contractor with …
This tutorial shows how to Exact match lookup with INDEX and MATCH in Excel using the example below; Formula {=INDEX(data,MATCH(TRUE,EXACT(val,lookup_col),0),col_num)} Explanation Case-sensitive lookup By default, standard lookups with VLOOKUP or INDEX + MATCH aren’t case-sensitive. Both VLOOKUP and MATCH will simply return the first match, ignoring case. However, if you need to do a case-sensitive …
This tutorial shows how to Match next highest value in Excel using the example below; Formula =INDEX(data,MATCH(lookup,values)+1) Explanation To match the “next highest” value in a lookup table, you can use a formula based on INDEX and MATCH. In the example shown, the formula in F6 is: =INDEX(level,MATCH(F4,points)+1) where “level” is the named range C5:C9, …
This tutorial shows how to Find lowest n values in Excel using the example below; Formula =SMALL(range,n) Explanation To find the n lowest values in a set of data, you can use the SMALL function. This can be combined with INDEX as shown below to retrieve associated values. In the example shown, the formula in …