Help with a Vlookup nested IF statement

Options

Hello! I would greatly appreciate some assistance in creating a formula to solve this. I've searched and tried multiple kinds of formulas and haven't found a solution yet.

I am trying to create a formula that will search for multiple matches across 2 sheets. The first has to match the Salesforce Case Number (column 1) in {Calibration: Chat (rev 3)} with the Salesforce Case Number (column 1) in {Calibration Key: Chat (rev)} before it can match (T/F) the criteria in the highlighted columns

My end goal is for the formula in the Score Helper column to produce a T or F depending if it matches but I need it to recognize and match the Salesforce Case Number to ensure it's looking at the correct key.

Future state will have multiple Keys listed so I need it to be able to match before it generates the T/F.

I have attempted to do a VLOOKUP with IF nested that didn't work. Not sure if a CONTAINS or INDEX would work better.

Please see the attached images of the 2 Sheets.


Best Answer

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓
    Options

    @Katherine Cline

    Not sure if this is what you are looking for, but will attempt:

    Replace the bolded cross sheet references as needed

    =IF(INDEX({Calibration Key - Initial Response Time}, MATCH([Salesforce Case Number]@row, {Calibration Key - Salesforce Case Number}, 0)) = [Initial Response Time]@row, "T", "F")

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!