Find lowest value matched from an Index/Match of an array
Current formula not returning values as desired:
=IFERROR(ifNA(min(arrayformula(index(D2:I2, match(INDEX(FLATTEN(split(A2, ", ", FALSE)&" "&TRANSPOSE(split(B2, ", ", FALSE)))), D$1:I$1, 0)))), "Role not at Facility"), "")
Column A contains a comma separated list of locations, Column B is a list of roles.
I want to check all combinations of a single location to a single role. arrayformula(FLATTEN(split(A2, ", ", FALSE)&" "&TRANSPOSE(split(B2, ", ", FALSE)))) is giving me an array of all combinations well enough.
I was hoping there was a way to feed that array into an index/match that would return all the matching values into an array so I could wrap that in a min() and get the lowest value of the array as my ultimate goal.
My sample sheet shows some of the problems I'm having, most obviously that the only location/role combo that gets evaluated is the first one.
Current state (formula in % Credentialed column)
| Location Credentialing For | Role Credentialing For | % Credentialed | VALLEY ROLE 1 | VALLEY ROLE 2 | VALLEY ROLE 3 | MISSION ROLE 1 | MISSION ROLE 3 | RIVER ROLE 2 |
|---|---|---|---|---|---|---|---|---|
| MISSION, RIVER | ROLE 1, ROLE 3 | 100.00% | 88.24% | 94.74% | 94.74% | 100.00% | 88.24% | 88.24% |
| MISSION, VALLEY, RIVER | ROLE 2 | Role not at Facility | 15.00% | 20.00% | 60.00% | 80.00% | 100.00% | 16.00% |
| VALLEY, MISSION | ROLE 2, ROLE 1 | 88.00% | 100.00% | 88.00% | 20.00% | 15.00% | 20.00% | 50.00% |
Desired return
| Location Credentialing For | Role Credentialing For | % Credentialed | VALLEY ROLE 1 | VALLEY ROLE 2 | VALLEY ROLE 3 | MISSION ROLE 1 | MISSION ROLE 3 | RIVER ROLE 2 |
|---|---|---|---|---|---|---|---|---|
| MISSION, RIVER | ROLE 1, ROLE 3 | 88.25% | 88.24% | 94.74% | 94.74% | 100.00% | 88.24% | 88.24% |
| MISSION, VALLEY, RIVER | ROLE 2 | 16.00% | 15.00% | 20.00% | 60.00% | 80.00% | 100.00% | 16.00% |
| VALLEY, MISSION | ROLE 2, ROLE 1 | 15.00% | 100.00% | 88.00% | 20.00% | 15.00% | 20.00% | 50.00% |
try:
=FLATTEN(INDEX(QUERY(IFNA(VLOOKUP(QUERY(SPLIT(FLATTEN(TRANSPOSE(FLATTEN(
ROW(A2:A5)&"×"&ROW(A2:A5)&SPLIT(A2:A5, ", ", )))&" "&
FLATTEN(SPLIT(B2:B5, ", ", )&"×"&ROW(B2:B5))), "×"),
"select max(Col2)
where Col1=Col3
and not Col2 matches '^\d+ .*|.* $'
and Col1 is not null
group by Col2
pivot Col3"), SPLIT(FLATTEN(ROW(A2:A5)&D1:I1&"×"&D2:I5), "×"), 2, 0)),
"select "&TEXTJOIN(",", 1,
"min(Col"&ROW(A2:A5)-(ROW(A2)-1)&")")), 2))
demo sheet with step by step formula explanation
- Finding min and max in array Javascript
- Python: slicing a multi-dimensional array
- How to count all objects in an array that match a condition?
- Why `[1, 2, 3]` is not showing as an `Array(3)` in Console?
- Cannot memory-free strings from arbitrary-length array of strings
- Explode delimited string into a 2d array with a specified key in each single-element row
- Why there is no F# immutable arrays?
- How to convert two longs to a byte array = how to convert UUID to byte array?
- C++ constexpr operations on each element of a constexpr array
- is there any array reverse indexing in c like python's a[-1]?
- Displaying binary images on JSP page
- Linear search in a sorted array - Java
- Convert flat array of delimited key path strings to a multidimensional array with each path terminating at an empty array
- How to iterate through an array of pointer in c
- Is there a way to create dynamic ranges given an array of values, and counting how many belong to each range
- Given an array of N integers, return the max sum of 3 NON-adjacent elements
- Parse array of strings composed of dot.separated.keys into a multidimensional array
- Explode each string in a flat array
- Parse a string with three delimiters into an associative array of indexed arrays
- Paginate Javascript array
- Page through an array
- Sum values based on three criteria and transpose result from tabular to visual layout
- Convert a JavaScript statement to a string
- Sort an array of associative arrays by column value
- Parse a multiline string with two delimiters per line into an array of associative rows
- Convert a string with 2 delimiters into a flat associative array
- How do I Split and filter 2 arrays
- PHP Array to List only values
- How to join / combine two arrays to concatenate into one array?
- Split a string by <br> tags wrapped in newlines