Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Nou pe simpatie:
Kitana pe Simpatie
 | Femeie
25 ani
Bucuresti
cauta Barbat
25 - 48 ani |
|
|
Mrrrr's Forum (VIEW ONLY)InregistrareLoginPozeNu sunteti logat. Lista Forumurilor Pe Tematici
|
| pus acum 18 ore#1 |
TRaPModerator
Postari: 967
|
|
Matching with INDEX-MATCH with less than/greater than will not yield the correct results.
results;Angajati!AD2# = what I want to return
family;Angajati!AE2# = family name of employee
s1;Angajati!AF2# = surname 1
s2;Angajati!AG2# = surname 2
s3;Angajati!AH2# = surname 3
names;O2:O9 = I have sets of 8 people for which I am printing internal permits for forklifts etc.
The formula below will bring info about these people (results) by searching in spills of their names. The Angajati sheet contains a list of names in one column, and in the spill columns I've split their names into columns. The formula below splits the names in cells O2:O9 into such items then searches each in the 4 columns in Angajati, in order. family is mandatory, s1 & s2 are either of them good. There are too few s3 so I didn't bother with that logic.
=LET(
results;Angajati!AD2#;
names;O2:O9;
family;Angajati!AE2#;
s1;Angajati!AF2#;
s2;Angajati!AG2#;
s3;Angajati!AH2#;
MAP(names;LAMBDA(n;
LET(
p;TEXTSPLIT(TRIM(n);" ";;TRUE);
cnt;COLUMNS(p);
f;CHOOSECOLS(p;1);
a;IF(cnt>=2;CHOOSECOLS(p;2);"");
b;IF(cnt>=3;CHOOSECOLS(p;3);"");
c;IF(cnt>=4;CHOOSECOLS(p;4);"");
m;(family=f)*
IF(cnt>=2;(((s1=a)+(s2=a))>0);1)*
IF(cnt>=3;(((s1=b)+(s2=b)+(s3=b))>0);1)*
IF(cnt>=4;(s3=c);1);
IFERROR(INDEX(results;XMATCH(1;m));"")
)
))
) |
Source: ChatGPT
|
|
| |
|
