1

I am not 100% sure VLOOKUP is the correct formula to use for this, but what I was trying was the following:

=ArrayFormula(IF(len(B2:B),vlookup(B2:B,{‘New1′!B3:C;’New2′!B3:C;’New3’!B3:C},2,FALSE),””))

I wanted to modify the above formula to do the following. I have a Google Sheet with multiple tabs. There is a single tab that has all of the data stored as metadata to populate the other 6 tabs. I am splitting the metadata into the 6 tabs, but want to avoid duplicating the data in the tabs. So I wanted to create a column that would show me where the data is currently placed if it is placed already. Here is an example:

Master Sheet has this data:

Name1, Phone1, Address1, E-mail1 
Name2, Phone2, Address2, E-mail2
Name3, Phone3, Address3, E-mail3 
Name4, Phone4, Address4, E-mail4

There will be 4 tabs where I have the same columns (Name, Phone, Address, E-mail). I will be splitting the data in the master sheet to these 4 tabs. If I place "Name2, Phone2, Address2, E-mail2" in Tab 3: I want the formula to see the entire chunk of data in the VLOOKUP, not just the name, and if it does, to display "Tab 3" in the field. There is metadata that has the same name or address, so I want the VLOOKUP to look across multiple tabs (Tab 1, Tab 2, Tab 3, Tab 4) and compare against multiple columns to match (Name, Phone, Address, E-mail are all the same as the ones in the master sheet) and if it finds that match...it will display some sort of indication of the tab it is on ("Tab 3"). Hopefully, this makes sense.

player0
  • 124,011
  • 12
  • 67
  • 124
Chris Moretti
  • 585
  • 3
  • 13
  • 31

1 Answers1

1
=ARRAYFORMULA(IFERROR(SUBSTITUTE(SPLIT(TRIM(
    TRANSPOSE(QUERY(TRANSPOSE(SUBSTITUTE(IF(LEN(A2:A), {
 IF(TRANSPOSE(QUERY(TRANSPOSE(A2:D),,999^99))=IFERROR(VLOOKUP(
    TRANSPOSE(QUERY(TRANSPOSE(A2:D),,999^99)),
    TRANSPOSE(QUERY(TRANSPOSE(Sheet1!A2:D),,999^99)),1,0)), "Sheet1", ),
 IF(TRANSPOSE(QUERY(TRANSPOSE(A2:D),,999^99))=IFERROR(VLOOKUP(
    TRANSPOSE(QUERY(TRANSPOSE(A2:D),,999^99)),
    TRANSPOSE(QUERY(TRANSPOSE(Sheet2!A2:D),,999^99)),1,0)), "Sheet2", ),
 IF(TRANSPOSE(QUERY(TRANSPOSE(A2:D),,999^99))=IFERROR(VLOOKUP(
    TRANSPOSE(QUERY(TRANSPOSE(A2:D),,999^99)),
    TRANSPOSE(QUERY(TRANSPOSE(Sheet3!A2:D),,999^99)),1,0)), "Sheet3", ),
 IF(TRANSPOSE(QUERY(TRANSPOSE(A2:D),,999^99))=IFERROR(VLOOKUP(
    TRANSPOSE(QUERY(TRANSPOSE(A2:D),,999^99)),
    TRANSPOSE(QUERY(TRANSPOSE(Sheet4!A2:D),,999^99)),1,0)), "Sheet4", )}, ),
 " ", "♦")),,999^99))), " "), "♦", " ")))

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • That is sort of what I was trying to attempt, but it is giving me strange results. Basically I want to search all of my tabs (Tabs 1-4) and look for a match of all of the columns. So if column A-D in any of the 4 tabs match against columns A-D in the master sheet, then fill in the field with the tab it was found in. So if it matched in Tab 2 and Tab 3, it should transpose Tab 2 and Tab 3 in the next 2 fields. – Chris Moretti Apr 26 '19 at 21:34
  • I made a sample sheet that has an example of the data and what I am looking for: https://docs.google.com/spreadsheets/d/1Eo--tRy99DtffUkukOQME-rpHOL0F56F7EGne2klgsU/edit?usp=sharing – Chris Moretti May 01 '19 at 12:23
  • The column in yellow on the last tab is what I am looking for. A formula that checks each of the 4 tabs before it to find exact matches for the entire 4 columns. There isn't a primary key to check in my data, so I would need it to check all 4 columns. If all 4 columns match the 4 columns in any of the 4 tabs, it will show the name of that tab as the result. If there are multiple results (the 4 columns match the 4 columns in multiple tabs) it will transpose those results for me. – Chris Moretti May 01 '19 at 12:25
  • Follow up to this. I broke the formula today and can't figure out what part of it is broken. The data I am referencing in the tabs is no longer literal. I am filling the data dynamically with a query. So I put a query formula in Sheet1:A2 and it populates all of the data in that sheet. I did that in each sheet. So I think, when this formula is trying to look at that data, there is nothing in the cells? The error I get is "In ARRAY_LITERAL, an Array Literal was missing values for one or more rows." – Chris Moretti Jun 28 '19 at 15:58
  • 1
    its hard to tell just like that from nothing in my hands. but array literal error means that matrix is not equal on both (all) sides in VR constellation. example of error: `={"a","b";"c"}` the fix: `={"a","b";"c","d"}` – player0 Jun 28 '19 at 16:15
  • 1
    You nailed it. I was just being forgetful. I have an array literal in the front of the formula to place a column header above the results. You reminded me that it was there. Problem solved, thanks man! – Chris Moretti Jun 28 '19 at 17:16