'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% |
Solution 1:[1]
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
Solution 2:[2]
Leaving the above answer as the official answer but adding that, if an array result is not desired, like for a large enough dataset that is regularly updated and would suffer like this, I used the above answer to par down to the below that also works:
=min(ArrayFormula(IFNA(ArrayFormula(VLOOKUP(FLATTEN(ArrayFormula(TRANSPOSE(flatten(SPLIT(A2, ", ", )))&" "&Transpose(SPLIT(B2, ", ", )))), ArrayFormula(SPLIT(FLATTEN(ArrayFormula(D1:I1&"×"&D2:I2)), "×")), 2, 0)))))
Please see player0's answer for the array return version of the above.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | |
| Solution 2 | NomadicPeach |