CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Teed Ferguson
Teed Ferguson

Reputation: 317

How do I find a name in a range in Sheets?

I am creating a Google Sheet that helps to organize students and sends emails to the appropriate parties. Part of this is finding a student's advisor.

Currently, I have two tabs, one with a list of students with their advisor and another with a list of all the students. I am trying to make a function on the student list that will search the advisory sheet and return the name of their advisor. All names are identically written between the two tabs, so "Jane Doe" on one would not be written "Doe, Jane" on the other. As far as strings go, "Jane Doe" == "Jane Doe" between the tabs.

Here is a link to a smaller, fake version of the real spreadsheet

TIA

Upvotes: 0

Views: 38

Answers (3)

Harun24hr
Harun24hr

Reputation: 36860

Give a try on below formula

=ArrayFormula(INDEX(Advisories!$A$1:$C$1,,MAX((Advisories!$A$2:$C$4=A2)*(COLUMN(Advisories!$A$2:$C$4)))))

enter image description here

Upvotes: 0

player0
player0

Reputation: 1

use:

=INDEX(IFNA(VLOOKUP(A2:A, 
 SPLIT(FLATTEN(Advisories!A2:D&"♦"&Advisories!A1:D1), "♦"), 2, 0)))

enter image description here

Upvotes: 1

GoranK
GoranK

Reputation: 1668

I suggest you to create a custom function below. Using standard functions would be too complicated. After you paste this code to you script editor and save, place the formula ={"Advisor";mylookup(A2:A,Advisories!$A$1:$C)} to the B1 cell

function myLookup(students, advisorsStudents) {
  const advisors = advisorsStudents[0]
  const studentsData = advisorsStudents.slice(1) 
  const assignedAdvisors = students.map(student=>{
    if(student==""){
      return ""
    }
    const rowFound = studentsData.find(row=>row.find(x=>x==student))
    if(rowFound){
      const index = rowFound.findIndex(x=>x==student)  
      return advisors[index]
    } else{
      return "advisor not found"
    }
  })
  return assignedAdvisors.map(a=>[a])
}

enter image description here

Upvotes: 0

Related Questions