Do you maybe also have a short explanation on the formula?
Of course, it will not be short because it has several functions.
First, tell you that it is an array formula, this means that it will analyze each of the values found in the arrays and that it will also compare several arrays.
I will divide the formula into parts:
a) From the range
$A$2:$C$9<>"" (array 1), it only considers the cells that have a value, the empty ones are not considered because in the next part of the formula a 0 is added to the value, but if you add 0 + "" that raises an error, that's why they shouldn't require the empty cells. All the cells that meet the condition will return a TRUE value.
b)
(LEFT($A$2:$C$9.4)+0<=$E2) (array 2), From this range it obtains the left part of each value, as the result of the
LEFT function returns a text, it is necessary to add a 0 to convert it to a numerical value, this numerical value is verified to be less than or equal to the value of E2. All the cells that meet the condition will return a TRUE value.
c)
(RIGHT($A$2:$C$9,4)+0>=$E2) (array 3), From this range it obtains the left part of each value, as the result of the
RIGHT function returns a text, it is necessary to add a 0 to convert it to a numerical value, this numerical value is verified to be greater than or equal to the value of E2. All the cells that meet the condition will return a TRUE value.
d)
(COLUMN($B$1:$D$1))))-1) The
COLUMN function returns the column number
(array 4), in this example it returns 2, 3 and 4.
All the true results of the 3 matrices are compared, which will give us the column number as a result, 1 is subtracted from this column number, which will give us 1, 2 or 3, which in the example are columns A, B or C. If the number was not found in any ZIP code combination, then it returns a 0 minus 1 the result is -1.
e)
MAX, The
MAX function is used, because only some functions work with matrices, in this example the result can be 0, 1, 2, or 3, so matrix 4 could return something like this: 0, 2, 0, the
MAX function takes the maximum value which is 2.
f)
INDEX($A$1:$C$1,0, parts a-e), The
INDEX function returns the value of a cell, you must tell it the range ($A$1:$C$1), the row number (0) means that no row is moved, and the column number, in this case the column was calculated with the previous functions (parts a,b,c,d and e).
g)
IFERROR(parts a-f,"Not exists"), The
IFERROR function evaluates the result of the formula if it is not an error, then the result is simply left, but if it is an error, the error occurs when the postal code was not found so the column is -1, since -1 is not in the references of the range of the INDEX function since it only has 1, 2 and 3 as references, then it returns error, then the result is "No exists".
To see how this fomula works or any function works:
1. Select the cell with the formula.
2. On the Formulas tab, in the Formula Auditing group, click Evaluate Formula.
3. Click Evaluate button to examine the value of the underlined reference. The result of the evaluation is shown in italics.
___________
I hope this helps.
Cordially
Dante Amor
___________
