Can I nest the IF function inside of the Filter Function?

Question

Can I nest the IF or any other function inside of the FILTER function to return maybe 1 instead of GBP in column J and return LON instead of LONDON in column B?

Answer 1

As per our discussion in the comments above, it would be more efficient to manipulate the results of the FILTER() function by nesting it inside of another function, rather than attempting to manipulate the entire data set. One such function that would work in this situation is the SWITCH() function:

=LET(
arr, FILTER(A2:E1000, (A2:A1000="MS")*(E2:E1000<>0)),
SWITCH(arr, "GBP", 1, "LONDON", "LON", arr))

SWITCH() looks at each value in the filtered array and replaces any matching values found with their corresponding results. Matching values and results are entered in pairs and can include up to 126 pairs in total. The final optional argument is the default value to be returned if no match is found, which in this case is the original value present in the filtered array. Also, SWITCH() only performs an exact match, so you can't use wildcards or comparison operators.

The above-mentioned formula is the quick and easy method, which works with the sample data you provided; however, it could be prone to errors if "GBP" and/or "LONDON" are present in more than one column. Furthermore, if you're actual data set contains multiple city names in the "Address" column (ie: Birmingham, Glasgow, Liverpool, etc.), managing the list of values and their corresponding results within SWITCH() can become quite cumbersome. As such, it would be more appropriate to identify the specific columns in the filtered array that you wish to manipulate and perform the applicable action on each individual column:

=LET(
arr, FILTER(A2:E1000, (A2:A1000="MS")*(E2:E1000<>0)),
add, LEFT(INDEX(arr,,2), 3),
cur, SWITCH(INDEX(arr,,4), "GBP", 1, "EUR", 2, "USD", 3),
CHOOSECOLS(HSTACK(arr, add, cur), 1, 6, 3, 7, 5))

In this example, INDEX() was used to identify the entire "Address" column by omitting the row_num argument and setting the column_num to 2, and LEFT() was used to simply return the first 3 letters of each value. The "Currency" column was identified in the same manner with column_num 4, and SWITCH() was used to replace the currency codes with a numeric value. This method works fine if your data set only contains a limited number of currency codes. I didn't specify the final default value argument in this case, so SWITCH() will return #N/A if a new code is present (ie: "CAD"), and you'll know the formula needs to be updated with an additional pair of values and results.

HSTACK() is then used to create a new array consisting of the filtered array, plus the new "add" and "cur" columns (7 columns in total), and CHOOSECOLS() is used to replace the original column 2 with the new column 6, as well as 4 with 7. That's it!

On a side note, I'm not a fan of using entire column references like A:E, A:A and E:E as this will significantly reduce the overall performance of the workbook (you'll likely notice a lag or hesitation when inputting new values in the referenced columns). Structured tables are ideal because the column references are dynamic and expand automatically when new records are added. However, if you prefer standard range references, I highly recommend using a reasonable number of rows that extends far enough below your existing data to allow for growth. For example: if your dealing with only a few hundred rows of data, A2:E1000, A2:A1000 and E2:E1000 would be adequate, and it's far more efficient than A1:E1048576 (which is what A:E is referencing).

Cheers!