IF string results in blank than give back text

Options
Joseph Adams
Joseph Adams ✭✭✭✭
edited 10/06/22 in Formulas and Functions

Hello,


I have the below formula that looks at two different sheets to determine the status of a test result.

The first index collect logic is looking for the Lot ID AND the test type are both found on the same row of the Test Log sheet. If he has found both then it looks up what the test status is and returns it back to me.

The second logic only runs if the first logic returns and error (meaning not found on the Test Log) and looks to see if the sample is still in the Test queue and if so then returns "Pending".

These both work just fine as is. However I would like to have it where if both of these return nothing or error out then i get back "N/A". Right now all the IF(NOT), ISBLANK and IFERROR combos either give me a blank result or "Pending". In the event where the a test sample requires multiple test types, I have built out this formula to just sub in the "Incorporation" for the other assay types I am looking for, however some test samples do not need ALL of the test types and so there will be instances where the Lot ID will be found on the queue but doesnt require "Incorporation" testing and needs a different test. This is the case I want to return an "N/A" for.

=IFERROR(INDEX(COLLECT({QC Result - Enzyme Test Log}, {Test Log - Lot ID Helper}, [Lot ID Helper]@row, {Test Log - Assay Type}, "Incorporation"), 1), IF(HAS({Enzyme Queue Lot ID}, [Lot ID Helper]@row), "Pending"))

Tags:

Best Answer

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓
    Options

    @Joseph Adams

    It sounds like you know enough about formulas so instead of typing another one out I'll save time and ask you to try this. Put the IF(HAS at the beginning so it will check that first to see if it needs to return "Pending" if it's still in the test queue. As the false condition for that, then use the IFERROR. As the IFERROR when it returns an error, have it put in "N/A". I think that may work having it in that order.

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @Joseph Adams

    Does this work?

    =IFERROR(IFERROR(INDEX(COLLECT({QC Result - Enzyme Test Log}, {Test Log - Lot ID Helper}, [Lot ID Helper]@row, {Test Log - Assay Type}, "Incorporation"), 1), IF(HAS({Enzyme Queue Lot ID}, [Lot ID Helper]@row), "Pending")), "N/A")

  • Joseph Adams
    Joseph Adams ✭✭✭✭
    edited 10/06/22
    Options

    That was one of my first attempts. It works when the LOT ID is found along side the assay type, however if the LOT ID is not found I just get a blank cell. No "N/A" for some reason.

    If the LOT ID is found but the assay type isnt, then it returns "Pending".

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓
    Options

    @Joseph Adams

    It sounds like you know enough about formulas so instead of typing another one out I'll save time and ask you to try this. Put the IF(HAS at the beginning so it will check that first to see if it needs to return "Pending" if it's still in the test queue. As the false condition for that, then use the IFERROR. As the IFERROR when it returns an error, have it put in "N/A". I think that may work having it in that order.

  • Joseph Adams
    Joseph Adams ✭✭✭✭
    Options

    Silly of me to not think of switching that order. It worked after doing some additional tinkering:


    =IF(AND(HAS({Enzyme Queue Lot ID}, [Lot ID Helper]@row), HAS({Enzyme Q Assay Type}, "Incorporation")), "Pending", IFERROR(INDEX(COLLECT({QC Result - Enzyme Test Log}, {Test Log - Lot ID Helper}, [Lot ID Helper]@row, {Test Log - Assay Type}, "Incorporation"), 1), "N/A"))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!