lookup value in google sheets-CodePudding

I need some help with google sheets lookup. I tried using VLOOKUP to do this, but wasn’t successful. I’m a google sheets beginner so need some help getting started.

I have 2 google sheets.

This is sheet1

Name
Emma
Kate
Tori
Rick
Jack
Topp
Brit

This is sheet2

Level1 Level2 Level3 State   City
Brit   Tori   Kate   Florida Orlando
Brit   Tori   Rick   Alabama Gunther
Brit   Topp   Rick   Texas   Dallas
Jack   Emma   Rick   Ohio    Dayton
Jack   Emma   Rick   Utah    Puntin
Jack   Emma   Rick   Maine   Lunop

I want to add 2 columns to sheet1 so that it looks like this.

Name State                     City
Emma Ohio, Utah, Maine         Dayton, Puntin, Lunop
Kate Florida                   Orlando
Tori Florida, Alabama, Texas   Orlando, Gunther, Dallas
…and so on for the rest.
Rick
Jack
Topp
Brit

CodePudding user response：

For State column use-

=JOIN(", ",FILTER(Sheet2!D$2:D,MMULT(--(Sheet2!$A$2:$D=$A2),SEQUENCE(COLUMNS(Sheet2!$A$2:$D$2)))))

Then drag down and across as needed.

CodePudding user response：

Use this formula for State

=IFNA(TEXTJOIN(", ",1,FILTER(Sheet2!$D$2:$D, Sheet2!$B$2:$B=A2) ),"")

And this formula for City

=IFNA(TEXTJOIN(", ",1,FILTER(Sheet2!$E$2:$E, Sheet2!$B$2:$B=A2) ),"")

CodePudding user response：

use:

=ARRAYFORMULA(IFNA(REGEXREPLACE({
 VLOOKUP(A2:A, TRIM(SPLIT(FLATTEN(QUERY(QUERY(SPLIT(FLATTEN(
 SEQUENCE(COUNTA(E2:E), COUNTA(E2:G))&"×"&E2:G&"♥×"&H2:H&","), "×"), 
 "select max(Col3) where Col2 is not null 
  group by Col1 pivot Col2"),,9^9)), "♥")), 2, 0), 
 VLOOKUP(A2:A, TRIM(SPLIT(FLATTEN(QUERY(QUERY(SPLIT(FLATTEN(
 SEQUENCE(COUNTA(E2:E), COUNTA(E2:G))&"×"&E2:G&"♥×"&I2:I&","), "×"), 
 "select max(Col3) where Col2 is not null 
  group by Col1 pivot Col2"),,9^9)), "♥")), 2, 0)}, ",$", )))