Explanation
In this example, the goal is to find the longest text string in the range C5:C16 that belongs to the group entered in cell F5. The group is a variable that may be changed at any time. At the core, this is a lookup problem, and the challenge is that the value we need to look up (the string length) does not exist in the data. We need to create this as part of the formula. The easiest way to solve this problem is with the XLOOKUP function . However, in older versions of Excel without the XLOOKUP function, you can use an INDEX and MATCH formula . Both approaches are explained below. For convenience, group (B5:B16) and name (C5:C16) are named ranges , but you can use regular cell references as well.
XLOOKUP solution
In the workbook shown, the XLOOKUP function returns the longest name in the range C5:C16 when the group value in B5:B16 equals the group entered in cell F5 (which contains “A” in the example shown). The formula in E6 is:
=XLOOKUP(MAX(LEN(name)*(group=F5)),LEN(name)*(group=F5),name)
where group (B5:B16) and name (C5:C16) are named ranges . The result is “Jonathan”, which contains 8 characters and is the longest name in group A. The tricky part of this formula is the way we apply the criteria for group , which is done with Boolean algebra . Working from the inside out, we first use the LEN and MAX functions to get the length of the longest name in group A like this:
MAX(LEN(name)*(group=F5))
The LEN function returns the length of a text string in characters. In this case, there are 12 names in data (C5:C16), so LEN returns 12 results in an array like this:
{5;6;8;6;6;5;6;8;9;6;8;6}
We then multiply this array by the expression (group = F5):
=MAX({5;6;8;6;6;5;6;8;9;6;8;6})*(group=F5))
=MAX({5;6;8;6;6;5;0;0;0;0;0;0})
=8
What happens here is that the results from LEN that are not part of group A are “cancelled out” and become zero. The result is returned directly to the MAX function, which returns 8. The result from MAX is delivered to the XLOOKUP function as the lookup_value :
=XLOOKUP(8,LEN(name)*(group=F5),name)
At this point, we have a lookup value of 8, and we need to create a lookup array that holds all string lengths that belong to group A. To do this, we repeat the same process above to create the lookup_array :
=LEN(name)*(group=F5)
={5;6;8;6;6;5;6;8;9;6;8;6}*(group=F5)
={5;6;8;6;6;5;0;0;0;0;0;0}
We can now simplify the formula to this:
=XLOOKUP(8,{5;6;8;6;6;5;0;0;0;0;0;0},name)
XLOOKUP performs an exact match by default. It matches the 8 in the lookup array and returns the corresponding value in name (“Jonathan”) as a final result.
INDEX and MATCH solution
In older versions of Excel without the XLOOKUP function, you can use an array formula based on INDEX and MATCH like this:
=INDEX(name,MATCH(MAX(LEN(name)*(group=F5)),LEN(name)*(group=F5),0))
Note: This is an array formula and must be entered with control + shift + enter in Excel 2019 and earlier.
Explanation
The goal is to identify invoice numbers in range D5:D11 that are missing in range B5:B16 (named list ). Two good ways to solve this problem in Excel are the COUNTIF function and the MATCH function . Both approaches are explained below.
COUNTIF function
COUNTIF counts cells in a range that meet a given condition (criteria). If no cells meet the criteria, COUNTIF returns zero. The generic syntax for COUNTIF is:
=COUNTIF(range,criteria)
To check for missing invoices, combine COUNTIF with the IF function . The formula in G5 is:
=IF(COUNTIF(list,D5),"OK","Missing")
Notice the COUNTIF formula appears inside the IF function as the logical_test argument. Normally, the IF function requires a logical test to return TRUE or FALSE. However, Excel will automatically evaluate any non-zero number as TRUE and the number zero (0) as FALSE. As the formula is copied down column E, COUNTIF returns the count of each invoice in column D in the named range list (B5:B16). When the count is non-zero, The IF function returns “OK”. If the invoice is not found in list , COUNTIF returns zero (0), which evaluates as FALSE, and IF returns “Missing”.
Alternative with MATCH
Another approach is to use the MATCH function . MATCH locates the position of a value in a row, column, or table. The generic syntax for MATCH in exact match mode is:
=MATCH(value,array,0)
The last value, match_type, is set to zero for an exact match. When MATCH finds the lookup value, it returns the position of that value in the array as a number . If MATCH doesn’t find the lookup value, it returns an #N/A error. Use this behavior to build a formula that returns “Missing” or “OK” by testing the result of MATCH with the ISNUMBER function :
=IF(ISNUMBER(MATCH(D5,list,0)),"OK","Missing")
As the formula is copied down column E, MATCH returns the position of each invoice in column D in the named range list (B5:B16). When an invoice number is not found, MATCH returns #N/A. The ISNUMBER function returns TRUE or FALSE, depending on the result from MATCH. The result from ISNUMBER is returned to the IF function as the logical_test , and IF then returns “OK” when an invoice is found and “Missing” when an invoice is not found. The screen below shows this formula in use:
