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 abbreviate text that contains capital letters, you can try this array formula based on the TEXTJOIN function, which is new in Excel 2016. You can use this approach to create initials from names, or to create acronyms. Only capital letters will survive this formula, so the source text must include capitalized words. You can …
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 + …
To calculate work hours between two dates with a custom schedule, you can use a formula based on the WEEKDAY and SUMPRODUCT functions, with help from ROW, INDIRECT, and MID. Formula =SUMPRODUCT(MID(schedule,WEEKDAY (ROW(INDIRECT(start&”:”&end))),1)*ISNA (MATCH(ROW(INDIRECT(start&”:”&end)),holidays,0))) Explanation In the example shown, the formula in F8 is: =SUMPRODUCT(MID(D6,WEEKDAY(ROW (INDIRECT(B6&”:”&C6))),1)*ISNA(MATCH (ROW(INDIRECT(B6&”:”&C6)),holidays,0))) Which returns 36 hours, based on a custom …
Set criteria to accept specific characters only To use data validation to allow a list of specific characters only, you can use a rather complicated array formula based on the COUNT, MATCH, and LEN functions. Formula =COUNT(MATCH(MID(A1,ROW(INDIRECT (“1:”&LEN(A1))),1),allowed&””,0))=LEN(A1) Explanation In the example shown, data validation is applied with this formula: =COUNT(MATCH(MID(B5,ROW(INDIRECT (“1:”&LEN(B5))),1),allowed&””,0))=LEN(B5) where “allowed” is …
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 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 …
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 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 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 …
This tutorial shows how to Find missing values in Excel using the example below; Formula =IF(COUNTIF(list,value),”OK”,”Missing”) Explanation If you want to find out what values in one list are missing from another list, you can use a simple formula based on the COUNTIF function. The COUNTIF function counts cells that meet supplied criteria, returning the …
This tutorial shows how to Get nth match with VLOOKUP in Excel using the example below; Formula =VLOOKUP(id_formula,table,4,0) Explanation To get the nth MATCH with VLOOKUP, you’ll need to add a helper column to your table that constructs a unique id that includes the count. If this isn’t practical, you can use an array formula based …
This tutorial shows how to calculate INDEX and MATCH descending order in Excel using the example below; Formula =INDEX(range1,MATCH(lookup,range2,-1)) Explanation To retrieve values from a table where lookup values are sorted in descending order [Z-A] you can use INDEX and MATCH, with MATCH configured for approximate match using a match type of -1. In the example …
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 calculate Two-way lookup with VLOOKUP in Excel using the example below; Formula =VLOOKUP(lookup_value,table,MATCH(col_name,col_headers,0),0) Explanation Note: Inside the VLOOKUP function, the column index argument is normally hard-coded as a static number. However, you can also create a dynamic column index by using the MATCH function to locate the right column. This technique allows you to …
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 Two-way lookup with INDEX and MATCH in Excel using the example below; Formula =INDEX(data,MATCH(val,rows,1),MATCH(val,columns,1)) Explanation To lookup in value in a table using both rows and columns, you can build a formula that does a two-way lookup with INDEX and MATCH. In the example shown, the formula in J8 …
This tutorial shows how to calculate Exact match lookup with SUMPRODUCT in Excel using the example below; Formula =SUMPRODUCT(–(EXACT(val,lookup_col)),result_col) Explanation Case sensitive lookups in Excel By default, standard lookups in Excel are not case-sensitive. Both VLOOKUP and INDEX/MATCH will simply return the first match, ignoring case. A direct way to workaround this limitation, is to use an array formula …
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 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 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 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 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 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 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 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 …