Advanced lookups

 Problem: How to lookup a value based on multiple criteria

Answer: Use the formula:

{=INDEX(C2:C11,MATCH(1,(F2=A2:A11)*(F3=B2:B11),0))}

where C2:C11 is the range containing the volume, F2 is the specific Customer name to search for, A2:A11 is the range containing the list of Customer names, F3 is the specific Product name to search for, B2:B11 is the range containing the list of Products. Please take note that this an array formula and must be entered using the combination of three keys: CTRL+SHIFT+ENTER. There is no need to include the braces "{}" in the formula. When you enter the array formula, Excel automatically assigns the braces in the array formula you entered.

An alternative formula is:

=XLOOKUP(1, (F2=A2:A11)*(F3=B2:B11), C2:C11)

This is not an array formula so there is no need to press CTRL-SHIFT-ENTER keys.

Explanation: The expression (F2=A2:A11)*(F3=B2:B11) is a logical expression that returns an array of values of either 1 or 0, based on the comparison of the values in cells F2 and F3 to the values in cells A2 to A11 and B2 to B11, respectively. Each value in the resulting array is the result of multiplying the corresponding values from the comparison of the individual cells. If both comparisons are true, then the corresponding value in the resulting array will be 1. If either comparison is false, then the corresponding value in the resulting array will be 0.

The MATCH function then looks for the first occurrence of the value 1 in this resulting array, and returns the relative position of that value. This relative position is used as the row num argument for the INDEX function, which returns the value in the specified range (C2:C11) that corresponds to that relative position. This formula returns the value in column C that corresponds to the first row where the values in cells F2 and F3 are equal to the values in the corresponding cells in columns A and B, respectively.

For the alternative formula, it has the following components:

"1" - This is the lookup_value argument. The XLOOKUP function will look for the first occurrence of this value in the range specified in the next argument.

"(F2=A2:A11)*(F3=B2:B11)" - This is the lookup_vector argument for the XLOOKUP function. It's a logical expression that returns an array of values of either 1 or 0, based on the comparison of the values in cells F2 and F3 to the values in cells A2 to A11 and B2 to B11, respectively. Each value in the resulting array is the result of multiplying the corresponding values from the comparison of the individual cells. If both comparisons are true, then the corresponding value in the resulting array will be 1. If either comparison is false, then the corresponding value in the resulting array will be 0.

"C2:C11" - This is the result_vector argument for the XLOOKUP function. It's the range of cells that the XLOOKUP function will use to return the desired value. In this case, it's a range of cells from C2 to C11.

This formula returns the value in column C that corresponds to the first row where the values in cells F2 and F3 are equal to the values in the corresponding cells in columns A and B, respectively. The XLOOKUP function performs this operation by searching for the first occurrence of the value 1 in the range specified in the second argument, and then returning the corresponding value in the range specified in the third argument.

Count instances of a specific character in a string

 Problem: How do I count the number of instances of a specific character in a string?

Answer: Use this formula:

=LEN(LOWER(D3))-LEN(SUBSTITUTE(LOWER(D3),D4,""))

where D3 is the string to be searched on, and D4 is the specific character you want to search

This example is not case sensitive, so if you want a formula that is not case sensitive, then change the formula to =LEN(D3)-LEN(SUBSTITUTE(D3,D4,""))

Explanation: The SUBSTITUTE function: SUBSTITUTE(LOWER(D3),D4,"")

removes all of the characters being counted in the original string. The length of this new string without the specific character is then subtracted from the length of the original string: LEN(LOWER(D3))-LEN(SUBSTITUTE(LOWER(D3),D4,"")), giving you the total instances of the specific character. You can use either UPPER or LOWER function, it does not really matter. This formula is not case sensitive so it will search for either "a" or "A". To make the formula case sensitive simply remove the LOWER or UPPER function in the formula, like this: LEN(D3)-LEN(SUBSTITUTE(D3,D4,"")).

Welcome to my Blog

Hi! I'm John and I'm excited to welcome you to my world of Excel and VBA! If you're looking for an exciting and educational journey into the depths of spreadsheet mastery, then you've come to the right place. In this blog, I will share my expertise and experience in tackling various Excel projects. I have over two decades of experience in working with MS Excel, beginning as a novice and gradually developing mastery over the years. Excel may be a straightforward tool, but it is complex in nature. With my blog, I want to help others unlock its full potential.