Formula for looking up values from one sheet to another using IF

Hello,

Would anyone be able to suggest the best formula to use for pulling information from first sheet to a second sheet if the criteria on a column of the first sheet is "yes"?

Basically if Column #48 of Sheet 1 is marked "Yes," then transfer the data from Columns #7 and #9 of Sheet 1 into Sheet 2. I tried an IF / VLOOKUP function but it didn't work. Could IF/INDEX MATCH or IF/COLLECT work? See below for what sheet 1 and sheet 2 looks like.


Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @jeanniesry_4,

    please try the following:

    1- you must have unique Row-ID in both Sheet1 & Sheet2.

    2- in sheet2 use the following formula to collect the value of column 7 ( Part ) when you check the cell in column 48 in sheet1

    =IFERROR(INDEX(COLLECT({Part}, {Row-ID}, [Row-ID]@row, {48}, @cell = 1), 1), "")
    

    3- in sheet2 use the following formula to collect the value of column 9 ( Lot ) when you check the cell in column 48 in sheet1

    =IFERROR(INDEX(COLLECT({Lot}, {Row-ID}, [Row-ID]@row, {48}, @cell = 1), 1), "")
    

    the following i a screenshot shows the result:

    sheet 1

    sheet 2


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!