+ Reply to Thread
Results 1 to 10 of 10

Need Formula to pull data from one sheet to another, if two columns' data matches

  1. #1
    Registered User
    Join Date
    03-08-2023
    Location
    Rockland County, NY
    MS-Off Ver
    Office 365
    Posts
    5

    Need Formula to pull data from one sheet to another, if two columns' data matches

    Hello there!

    I am looking for a formula that will pull data from one column into another, if a different two columns have matching values.

    Using the example provided:
    If tab "Form1" Column AZ data matches tab "Form2" Column B data, can the data from tab "Form1" Column AY be automatically populated into tab "Form2" Column F?
    ie. "??????" becomes "YYYYYY" automatically if Column AZ and Column B both contain "BBBBB"

    Thanks very much in advance for the assistance!
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Need Formula to pull data from one sheet to another, if two columns' data matches

    Is this just:

    =IF(Form1!AZ2=Form2!B2,Form1!AY2,"")

    copied down... or is there something I've missed??
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    03-08-2023
    Location
    Rockland County, NY
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Need Formula to pull data from one sheet to another, if two columns' data matches

    Thank you, that formula seems to work in that it brings the data over.

    Was wondering if there would be a way to automatically apply this formula to an entire column, so that every new row added
    to Form1 AY will automatically apply that data to Form2 Column F (if Form1 AZ and Form2 F match)?
    Otherwise, it seems like this formula would need to be manually applied to ever row in Form2 Column F with the adjusted row number.

    Does this make sense? I apologize, I am new to Excel formulas.
    Thanks again for your help.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Need Formula to pull data from one sheet to another, if two columns' data matches

    I suspect you are oversimplifying what you want.

    =IF(B2="","",IF(Form1!AZ2=Form2!B2,Form1!AY2,""))

    Copy down as far as needed.

  5. #5
    Registered User
    Join Date
    03-08-2023
    Location
    Rockland County, NY
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Need Formula to pull data from one sheet to another, if two columns' data matches

    Still having an issue, at least with this particular situation.

    The data from Form1 is coming from a survey submitted through Microsoft Forms.
    The formulas provided work if the data from each page is in the same row.

    However, the order surveys are submitted doesn't match the order of the data on Form2; we are unable to control this,
    as other people are submitting these surveys at their discretion.

    I've attached an edited example to better show what we're looking for.
    For example, is there a formula to pull the data from Form1 CellAY2, insert it into Form2 CellF4, if Form1 CellAZ2 and Form2 CellA4 match?
    Can this formula be copy-pasted to the entire Form2 F column to automatically do this, regardless of which row the data is in?

    Apologies if this is confusing or ambiguous, I appreciate your help.
    Attached Files Attached Files

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Need Formula to pull data from one sheet to another, if two columns' data matches

    Hahaha. I just KNEW you were oversimplifying it. So try:

    =IF(E2="","",INDEX(Form1!AY:AY,MATCH(Form2!A2,Form1!AZ:AZ,0)))

    or,if your O36s supports BYROW:

    =LET(A,Form1!AY2:AY2000,B,Form1!AZ2:AZ2000,C,Form2!A2:A6,D,BYROW(C,LAMBDA(x,FILTER(A,B=x))),FILTER(D,D<>""))

    will do it all in one cell

    see file for both possibilities.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-08-2023
    Location
    Rockland County, NY
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Need Formula to pull data from one sheet to another, if two columns' data matches

    Hello again,

    Unfortunately, we are still having issues with this formula.
    Though it seems to work just fine in the example, when applied to our actual spreadsheet both formulas return errors.
    See attached for the errors returned: with sensitive data blocked out.
    Also attached is an edited example with some more (fake) data added to give you a better idea of what our spreadsheet looks like, with the tab names changed to what they are one our actual spreadsheet for clarity's sake.

    The issue with the formulas seems to lie in what they are referencing.
    The IF formula should be pulling the data from Form1 Column AY and entering it into Surveys Column F, if Form1 Column AZ and Surveys Column B match.
    I am unsure why the formula provided is referencing column E.

    Apologies again for the confusion and thanks a heap for your help!
    Attached Images Attached Images
    Attached Files Attached Files

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Need Formula to pull data from one sheet to another, if two columns' data matches

    Try:
    =LET(A,Form1!AY2:AY2000,B,Form1!AZ2:AZ2000,C,Surveys!B2:B5,D,BYROW(C,LAMBDA(x,FILTER(A,B=x))),FILTER(D,D<>""))

    or (copied down)

    =IF(B2="","",INDEX(Form1!AY:AY,MATCH(Surveys!B2,Form1!AZ:AZ,0)))
    Attached Files Attached Files

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Need Formula to pull data from one sheet to another, if two columns' data matches

    You changed a few columns around... that's what's thrown it off. If you want to extend the spill formula:

    =LET(A,Form1!AY2:AY2000,B,Form1!AZ2:AZ2000,C,Surveys!B2:B5,D,BYROW(C,LAMBDA(x,FILTER(A,B=x))),FILTER(D,D<>""))

    further down surveys, column B to include BLANK rows.... amend to something like:

    =LET(A,Form1!AY2:AY2000,B,Form1!AZ2:AZ2000,C,Surveys!B2:B100,D,FILTER(C,C<>""),E,BYROW(D,LAMBDA(x,FILTER(A,B=x))),FILTER(E,E<>""))

    see file.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    03-08-2023
    Location
    Rockland County, NY
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Need Formula to pull data from one sheet to another, if two columns' data matches

    Hello again,

    Apologies, but this formula is still giving us errors.

    Let me just understand the logic of the formula and we'll assign the columns after.

    If two cells match, pull the data from another cell and insert into the cell with the formula.
    How can we write this formula without cell references?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] If data matches pull to a new sheet
    By alip_12345 in forum Excel General
    Replies: 12
    Last Post: 08-26-2021, 09:31 AM
  2. [SOLVED] How to pull in data from multiple columns if entry matches cell from other sheet
    By Mac101010 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-24-2021, 10:21 AM
  3. [SOLVED] need to pull data from another sheet that matches 2 criteria
    By allgoodthings in forum Excel General
    Replies: 2
    Last Post: 03-16-2021, 07:23 PM
  4. Formula (Index?) to pull data from one sheet to another based on cell data
    By kikijean in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-04-2021, 10:00 PM
  5. Replies: 1
    Last Post: 04-19-2019, 07:03 PM
  6. Pull data from one sheet to another if a company name matches
    By toddbn in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-30-2015, 06:08 PM
  7. Pull data from a specific sheet that matches cell value on Input Sheet
    By vaciaravino in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-12-2012, 08:38 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1