excelif-statementexcel-formula

Is there a better method than nested if/then to evaluate if cells in one set of columns has data, and then populate a different set of columns


I have a spreadsheet of products with all of their specifications. One group of columns indicate which certifications the products have, by putting an X, or "yes", or any character in the cell. (Columns A-D in the screenshot)

Using the screenshot as the example, what I would like to do is have another set of columns (E-H) that refer to columns A-D and populate the applicable certification image paths, filling them in from the left. So there should never be a gap.

For the screenshot, I entered the E-H cells manually just to show the desired outcome. I also shorted the text so the image wouldn't be super wide. An image path would be like "/volume/marketing/certs/UL.png".

I've got the first column to work using: =IF(ISBLANK(A10),(IF(ISBLANK(B10),(IF(ISBLANK(C10),(IF(ISBLANK(D10),"","UL-C")),"ESTAR")),"EMF")),"UL")

I've been trying nested IF and ISBLANK formulas for the F-H columns, but it's clear that it'll be a long formula, so I wanted to see if there was an easier way before I continue with the nested IF/ISBLANK method.

Thanks!

Screenshot of desired outcome


Solution

  • Cert Path
    EMF /volume/marketing/certs/EMF.png
    ESTAR /volume/marketing/certs/ESTAR.png
    UL /volume/marketing/certs/UL.png
    UL-C /volume/marketing/certs/UL-C.png
    =LET(certs, A1:D1, has_cert, --(A2:D8 <> ""),
        certs_maped, IF(has_cert, REPT(certs, has_cert), NA()),
        img_paths, XLOOKUP(certs_maped,imgPath[Cert], imgPath[Path]),
        acc_left, REDUCE(
            "Collect to left",
            SEQUENCE(ROWS(img_paths)),
            LAMBDA(acc,r, VSTACK(acc, IFERROR(TOROW(INDEX(img_paths, r, ), 2), "")))
        ),
        IFNA(DROP(acc_left, 1), "")
    )
    

    Result