Forum Discussion

  • djclements's avatar
    djclements
    Bronze Contributor

    anshul-marele What about it is not working as expected? By the way, the workbook you shared on OneDrive is open to everyone to make edits. There was a copy of the MAP formula entered in cell B8, which was causing the #SPILL! error for the formula in cell B4. Once the erroneous entry was removed from cell B8, the MAP formula in cell F4 worked as written. I edited your workbook directly on OneDrive, so it should be working now. I also added a formula demonstrating that MAP is not actually needed in this case:

     

    =MAP(A4#,B4#,
    LAMBDA(col_1,col_2,
    XLOOKUP(col_2&col_1,B4#&D4#,C4#)))

     

    ...can be accomplished using XLOOKUP only:

     

    =XLOOKUP(B4#&A4#,B4#&D4#,C4#)

     

    You can also specify a value to be returned in the [if_not_found] argument, such as "not found" or an empty text string ("") to remove the #N/A errors that would otherwise be returned for items where no match is found.

Resources