Search code examples
excelexcel-formulaxlookupindex-match

Both XLOOKUP and INDEX-MATCH aren't returning requested value

I work for a picture frame company, and we are trying to work out which specific parcels that get sent to our customers are accruing surcharges for being Out Of Gauge (overweight, oversized, etc).

We have a table that is sent to us by a courier company, containing various consignment data of which only some of relevant to this. I need to create a second table which should include only the consignment number, and the total consignment price, in a singular row. However, I can't seem to match the Out of Gauge rows with their parent rows.

The format for the data that is relevant is as follows:

Last Parcel Number Description Price Consignment Number All Parcels in Consignment
0001 Standard £ 5.00 CN001 0001
0003 Standard £ 10.00 CN002 0002;0003
0004 Standard £ 5.00 CN003 0004
0008 Standard £ 20.00 CN004 0005;0006;0007;0008
0010 Standard £10.00 CN005 0009;0010
0002 Out of Gauge £ 15.00
0009 Out of Gauge £ 15.00
0010 Out of Gauge £ 15.00

I figure I need to pair the Out of Gauge parcel number with a consignment number, and this is what I can't find a solution to. I feel as though I've been overcomplicating things.

Firstly, I'll mention that this new table needs to be refreshable, so all of these functions are enclosed in an IF statement as follows:

=IF( E2= "", \<function\>, E2)

Result: Correctly displays data when there is already a value in Column4.

As for when there is no data in Column4:

Initially I tried an XLOOKUP, and split the contents of Column5 into separate columns with TEXTSPLIT to use as the lookup array:

=XLOOKUP( A2, TEXTSPLIT( E2:E5, ";"), D2:D5)

Result: All of the Out of Gauge rows returned #N/A.

I also tried the TEXTSPLIT as a standalone function which displayed the result in the columns following on from Column5, in case the issue was that I was trying to TEXTSPLIT within the XLOOKUP, but the only difference is that it returned #VALUE instead.

I then tried INDEX-MATCH, but as far as I can tell it can't be done in that way as the return value is different from the lookup value. Please correct me if I'm wrong on this.

Any possible solutions would be greatly appreciated.

Solution

  • Assuming there is no Excel Constraints as per the tags posted, and in accordance with the readability and simplicity of the approach, one can use one of the followings, 3 methods shown, two uses LAMBDA() helper function called MAP() while one other needs to fill down!

    enter image description here

    • Formula used in cell G2

    =SUM(SUMIFS($C$2:$C$9,$A$2:$A$9,TEXTSPLIT(F2,";")))

    Or,

    • Formula used in cell H2

    =MAP(F2:F6,LAMBDA(α, SUM((TEXTSPLIT(α,";")=A2:A9)*C2:C9)))

    Or,

    • Formula used in cell I2

    =BYROW(F2:F6,LAMBDA(x,SUM((TEXTSPLIT(x,";")=A2:A9)*C2:C9)))

    Hope I am not over complicated the solutions. But the basic principles of all the formula solutions posted above uses minimal excel memory consumption so as one can work faster, effortlessly without making the excel engine to slow down.

    • In all the above approaches, one can see, i have used TEXTSPLIT() as a criteria, what does it do, it splits the cell values in All Parcels In Consignment by column wise using the delimiter as ;
    • Once it has been it has been used as a criteria in SUMIFS() function.
    • Since the TEXTSPLIT() will return an array of values, so when using with SUMIFS() it will return multiple values for the same, therefore to get the desired output we need to wrap it within a SUM() functon.
    • Last one using a LAMBDA() helper MAP() it uses the LAMBDA() custom function to iterate through each cells to perform the task it is assigned to and gives the relevant output for each cells.

    Addendum: All the return output can be merged with the Consignment Number Columns with HSTACK()