Map with Xlookup

Copper Contributor

 

Hi,

Map with Xlookup not working

 

Sheet

2 Replies

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

@djclements Sorry My Bad. I tried to apply the wrong formula. Thanks :folded_hands: