Hello fellow members,
I'm looking for help with a task of finding closest match between an array and array of arrays.
What I'm looking for is an array, say A1:A7 where A1 may be 0-9, A2 may be 0 or 10-19, A3 may be 0 or 20-29, A4 may be 0 or 30-39, A5 may be 0 or 40-49, A6 may be 0 or 50-59 and A7 may be 0 or 60-67. Max of three cells may be other than zero (and I don't know what cells in advance) - it may be e.g. (0;0;25;0;45;0;63) or (5;0;0;33;0;0;0)
I have a target array of 40 rows with 6 columns. Each column has several numbers (max - 6) also in the same way, but there may be even 6 numbers different from 0).
What I'm interested in is finding one or several closest matches. E.g.
1. I have (0;0;25;0;45;0;63) as a source and there are (0;0;25;0;45;0;63) in row 5 of large array and (0;0;25;0;0;0;63) in row 15 of large array - there are no 25 or 45 or 63 in other rows. I want to get a) two as a number of matches - one partial and one full match b) row numbers for these matches.
2. What I did is - set ISNUMBER(MATCH) for every cell comparison, calculated matches, obtained max number of matches and done INDEX/MATCH (VLOOKUP) for every match.
Is there an option to get it in a more usable way? I don't want to have a matrix of TRUE/FALSE because there is a number of arrays to compare actually.
Many thanks in advance, I'm lost in this. Tried SUMPRODUCT and COUNTIFS but they didn't work..
I'm looking for help with a task of finding closest match between an array and array of arrays.
What I'm looking for is an array, say A1:A7 where A1 may be 0-9, A2 may be 0 or 10-19, A3 may be 0 or 20-29, A4 may be 0 or 30-39, A5 may be 0 or 40-49, A6 may be 0 or 50-59 and A7 may be 0 or 60-67. Max of three cells may be other than zero (and I don't know what cells in advance) - it may be e.g. (0;0;25;0;45;0;63) or (5;0;0;33;0;0;0)
I have a target array of 40 rows with 6 columns. Each column has several numbers (max - 6) also in the same way, but there may be even 6 numbers different from 0).
What I'm interested in is finding one or several closest matches. E.g.
1. I have (0;0;25;0;45;0;63) as a source and there are (0;0;25;0;45;0;63) in row 5 of large array and (0;0;25;0;0;0;63) in row 15 of large array - there are no 25 or 45 or 63 in other rows. I want to get a) two as a number of matches - one partial and one full match b) row numbers for these matches.
2. What I did is - set ISNUMBER(MATCH) for every cell comparison, calculated matches, obtained max number of matches and done INDEX/MATCH (VLOOKUP) for every match.
Is there an option to get it in a more usable way? I don't want to have a matrix of TRUE/FALSE because there is a number of arrays to compare actually.
Many thanks in advance, I'm lost in this. Tried SUMPRODUCT and COUNTIFS but they didn't work..
Last edited: