Google Sheets – Using INDEX-MATCH for Nested Lookups

google sheets

I am trying moving from an access database to google spreadsheets.

In my workbook, I have 3 google sheets:

  1. merged_data (merge all data)
  2. studentsANDemployers (studentID, student_name, employerID)
  3. employers (employerID, employer_name)

I am trying to get something like this to work on my merged_data sheet.

    A           B              C
1   Student ID  Student Name   Employer
2   #245        Peter          ??? (return employer_name match)

In order to get C2: employer_name I need to run 2 matches. I know I can run it as 2 formulas using 2 columns but I am wondering if it is possible to set it up all in one?

I was trying to build the following:
Check merged_data A2 and MATCH StudentandEmployer! A:A
Where there is a MATCH, select the applicable D?? (EmployerID).
Then go to Employer! and MATCH StudentandEmployer!D?? with Employer!B:B and put result into C2.

I can set up each mindex match but don't know how to join them:

=INDEX(Employer!B:B,MATCH (A4,StudentandEmployer!A:A,0) ???...???
  MATCH(StudentandEmployer!D:D,Employer!A:A,0))

Best Answer

If I'm understanding correctly:

=INDEX(Employer!B:B;MATCH(INDEX(StudentandEmployer!D:D;MATCH(A4;StudentandEmployer!A:A;0));Employer!A:A;0))

Although I think in this situation using VLOOKUP instead of INDEX(MATCH()) might be a bit more straightforward:

=VLOOKUP(VLOOKUP(A4;StudentandEmployer!A:D;4;0);Employer!A:B;2;0)

Related Topic