Search code examples
filterconditional-statementsgoogle-sheets-formulavlookuprelate

Conditional Relate ID with email and name

I'm trying to create a relation from some tables we could that the ID is the team they belong to, and the division is their role.(IN google sheets)(This table of information is in Sheet7)

enter image description here

Now I'm trying to create a conditional IF B2(Value) is lower than 0.8 give me the name and email from the person with the id and the division corresponding to that value. Here is my formula but it's not working =vlookup(A2,if(B2=>0.8;Sheet7!A1:D16;""),2,0) some help please! (This values tables is in Sheet8)

enter image description here

Solution

  • try in F2:

    =INDEX(IF(B2:B5>=0.8, IFNA(VLOOKUP(A2:A, 
 FILTER({Sheet7!C:C, Sheet7!A:D}, Sheet7!D:D=B1), {2, 3}, 0)), "xxx"))

    update:

    =INDEX(IFNA(VLOOKUP(INDEX(SORT(QUERY(SPLIT(FLATTEN(
 COLUMN(B1:E1)&"×"&B1:E1&A2:A&"×"&SUBSTITUTE(B2:E; "."; ",")*1); "×"); 
 "where Col2 matches '.*\d+$' and Col3 < 0.8"));; 2); 
 {Sheet7!D:D&Sheet7!C:C\ Sheet7!A:D}; {2\ 3}; 0)))

    enter image description here