IF string results in blank than give back text

Joseph Adams

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"))

Mike TV

@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.

Mike TV

@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

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

@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

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"))