Returning a value based on multiple columns

[abukapsoun]

Hi,

Need your kind support for one tricky case I have. 

I have the following in column in sheet1

Pointer

X1

X2

X3

X4

X5

And the following table in sheet2

Pointer	Previous day	today	tomorrow
X1	10	0	0
X2	0	5	0
X5	15	15	15

I want to create a new column in sheet1 and populated with a text result after doing a search in sheet2 like the following:

Pointer	Where is it?
X1	Has value in previous day
X2	Has value in today
X3	Not Found
X4	Not Found
X5	Has value in previous day, today and tomorrow

Could you please help me finding out what would be the formula I should be using in the "Where is it?" column

Thank you, appreciated.

[#2]

@abukapsoun 

Yes it's possible to count the occurrences. Thank you for this suggestion. This formula returns the expected results in the attached sheet in my understanding.

=IF(AND(COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$M$2:$M$227,">"&0)>0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$N$2:$N$227,">"&0)>0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$Q$2:$Q$227,">"&0)=0),"Has value in previous and today",IF(AND(COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$M$2:$M$227,">"&0)=0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$N$2:$N$227,">"&0)>0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$Q$2:$Q$227,">"&0)=0),"Has value in today",IF(AND(COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$M$2:$M$227,">"&0)=0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$N$2:$N$227,">"&0)=0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$Q$2:$Q$227,">"&0)>0),"Has value in tomorrow",IF(AND(COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$M$2:$M$227,">"&0)>0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$N$2:$N$227,">"&0)=0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$Q$2:$Q$227,">"&0)=0),"Has value in previous day",IF(AND(COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$M$2:$M$227,">"&0)>0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$N$2:$N$227,">"&0)>0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$Q$2:$Q$227,">"&0)>0),"Has value in previous day, today and tomorrow","")))))

[#3]

Thank you very much @OliverScheurich , I am trying now to apply it on my dataset. Will be updating you, but meanwhile what does (">"&0) means in the formula if you don't mind? 

'OI Finalv3'!$M$2:$M$227,">"&0)>0

And I tried to add the IF part at the end, it is giving me too many arguments,

=IF(AND(COUNTIFS('OI Finalv3'!$H$2:$H$227,H12,'OI Finalv3'!$M$2:$M$227,">"&0)>0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H12,'OI Finalv3'!$N$2:$N$227,">"&0)>0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H12,'OI Finalv3'!$Q$2:$Q$227,">"&0)=0),"OI planned in PLE and LE",IF(AND(COUNTIFS('OI Finalv3'!$H$2:$H$227,H12,'OI Finalv3'!$M$2:$M$227,">"&0)=0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H12,'OI Finalv3'!$N$2:$N$227,">"&0)>0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H12,'OI Finalv3'!$Q$2:$Q$227,">"&0)=0),"Has value in today",IF(AND(COUNTIFS('OI Finalv3'!$H$2:$H$227,H12,'OI Finalv3'!$M$2:$M$227,">"&0)=0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H12,'OI Finalv3'!$N$2:$N$227,">"&0)=0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H12,'OI Finalv3'!$Q$2:$Q$227,">"&0)>0),"Has value in tomorrow",IF(AND(COUNTIFS('OI Finalv3'!$H$2:$H$227,H12,'OI Finalv3'!$M$2:$M$227,">"&0)>0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H12,'OI Finalv3'!$N$2:$N$227,">"&0)=0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H12,'OI Finalv3'!$Q$2:$Q$227,">"&0)=0),"Has value in previous day",IF(AND(COUNTIFS('OI Finalv3'!$H$2:$H$227,H12,'OI Finalv3'!$M$2:$M$227,">"&0)>0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H12,'OI Finalv3'!$N$2:$N$227,">"&0)>0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H12,'OI Finalv3'!$Q$2:$Q$227,">"&0)>0),"Has value in previous day, today and tomorrow","")))),IF(LEFT('NS Finalv3'!H2,3)="xxx","Not Found1","Not Found"))

[#4]

@abukapsoun 

The formula will look utterly alien to you but it is the way I work all the time and gradually ones thought processes get used to it.  First and foremost though, it needs Excel 365 (or 2021).

365 started to diverge from traditional Excel practice at the end of 2018 when array formulas became the default rather than having to use Ctrl/Shift/Enter to override the changes made during grid-based calculation to restrict the calculation to a scalar value.  In 2020, LET introduced named variables within a formula and soon after that LAMBDA allowed one to pass arguments to a formula.  This opens up the way to completely different ways of working, though most choose to stick pretty close to what they know.

As for my formula, the worksheet formula

= MAP(pointer, IdentTimingλ)

passes terms of 'pointer', {"X1";"X2"; "X3"; "X4"; "X5";} to the Lambda function 'IdentTimingλ' (identify time periods) one at a time and collects the results as an array.

As for the content of the Lambda function

= LET(
    row, XLOOKUP(ptr, tablePtr, table, 0),
    hdr, FILTER(timing, row > 0, ""),
    IF(@hdr <> "", "Has value in " & TEXTJOIN(", ", , hdr), "Not found")
  )

the first line uses XLOOKUP to return the row of your table that corresponds to a particular value of the pointer, "X1" (say).  FILTER looks for positive numbers in the row and uses that as a criterion to return a subset of {"previous day", "today", "tomorrow"} from the header, as an array. The final row concatenates the result array into something close to the text you specified.

The Lambda function itself may be viewed within Name Manager or by downloading the AFE (advanced formula editor).  If you have 365, you could just use the formula without understanding it but, most likely, it would be better to sort what you are doing now and explore the new methods when you have the time and inclination to do so.

[#5]

@abukapsoun 

COUNTIFS('OI Finalv3'!$H$2:$H$227,H12,'OI Finalv3'!$M$2:$M$227,">"&0)

Within the COUNTIFS this means that the value in range M2:M227 is counted if it's greater than 0.

In the sample file the result of the formula for pointer x5 for the previous day is therefore 1.

This is how we can add the additional IF statement in case the pointer is missing in the other sheet and if it starts with "xxx".

=IF(AND(COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$M$2:$M$227,">"&0)>0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$N$2:$N$227,">"&0)>0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$Q$2:$Q$227,">"&0)=0),"Has value in previous and today",IF(AND(COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$M$2:$M$227,">"&0)=0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$N$2:$N$227,">"&0)>0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$Q$2:$Q$227,">"&0)=0),"Has value in today",IF(AND(COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$M$2:$M$227,">"&0)=0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$N$2:$N$227,">"&0)=0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$Q$2:$Q$227,">"&0)>0),"Has value in tomorrow",IF(AND(COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$M$2:$M$227,">"&0)>0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$N$2:$N$227,">"&0)=0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$Q$2:$Q$227,">"&0)=0),"Has value in previous day",IF(AND(COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$M$2:$M$227,">"&0)>0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$N$2:$N$227,">"&0)>0,COUNTIFS('OI Finalv3'!$H$2:$H$227,H2,'OI Finalv3'!$Q$2:$Q$227,">"&0)>0),"Has value in previous day, today and tomorrow",IF(AND(ISNA(MATCH(H2,'OI Finalv3'!$H$2:$H$227,0)),LEFT('NS Finalv3'!H2,3)="xxx"),"Not Found1","Not Found"))))))

[#6]

Thank you, thank you, thank you, thank you.. it seems all working as expected now!! @OliverScheurich I will dry run the data on my bigger dataset and get back to you in case needed (I hope not :) ) 

[#7]

Thank you @Peter Bartholomew, Yes I have O365 and I am interested to look into that new area. But one thing, When I opened you earlier excel sheet, I could only find that piece of code "= _xlfn.MAP(pointer, IdentTimingλ)", I am not able to find where you have inserted the LET function 

[#8]

@abukapsoun 

The formula

= MAP(pointer, IdentTimingλ)

is in cell C4 of Sheet1.  It looks as if the sheet has been opened in legacy Excel and the returned text shows that the MAP function is unknown.  Is the Lambda function itself visible within Name Manager or has that been scrambled too?

[#9]

@abukapsoun 

Somewhere along the line, multiple matches to a given pointer seemed to creep in.  That would prevent me using XLOOKUP, since that will return only the first matching row.  To sum over the matching rows, I would need to change the formula to

=LET(
    row, BYCOL(FILTER(table, tablePtr=ptr, 0), Sumλ),
    hdr, FILTER(timing, row > 0, ""),
    IF(@hdr <> "", "Has value in " & TEXTJOIN(", ", , hdr), "Not found")
)

where Sumλ contains
= SUM(x)

I haven't followed the developments closely enough to know whether there are further changes to the problem specification.

[#10]

@OliverScheurich 

This is your formula in the AFE.

=IF(
    AND(
        COUNTIFS(
            directReferenced!$B$2:$B$227, B2,
            directReferenced!$C$2:$C$227, ">" &
                0
        ) > 0,
        COUNTIFS(
            directReferenced!$B$2:$B$227, B2,
            directReferenced!$D$2:$D$227, ">" &
                0
        ) > 0,
        COUNTIFS(
            directReferenced!$B$2:$B$227, B2,
            directReferenced!$E$2:$E$227, ">" &
                0
        ) = 0
    ),
    "Has value in previous and today",
    IF(
        AND(
            COUNTIFS(
                directReferenced!$B$2:$B$227, B2,
                directReferenced!$C$2:$C$227, ">" &
                    0
            ) = 0,
            COUNTIFS(
                directReferenced!$B$2:$B$227, B2,
                directReferenced!$D$2:$D$227, ">" &
                    0
            ) > 0,
            COUNTIFS(
                directReferenced!$B$2:$B$227, B2,
                directReferenced!$E$2:$E$227, ">" &
                    0
            ) = 0
        ),
        "Has value in today",
        IF(
            AND(
                COUNTIFS(
                    directReferenced!$B$2:$B$227, B2,
                    directReferenced!$C$2:$C$227, ">" &
                        0
                ) = 0,
                COUNTIFS(
                    directReferenced!$B$2:$B$227, B2,
                    directReferenced!$D$2:$D$227, ">" &
                        0
                ) = 0,
                COUNTIFS(
                    directReferenced!$B$2:$B$227, B2,
                    directReferenced!$E$2:$E$227, ">" &
                        0
                ) > 0
            ),
            "Has value in tomorrow",
            IF(
                AND(
                    COUNTIFS(
                        directReferenced!$B$2:$B$227, B2,
                        directReferenced!$C$2:$C$227, ">" &
                            0
                    ) > 0,
                    COUNTIFS(
                        directReferenced!$B$2:$B$227, B2,
                        directReferenced!$D$2:$D$227, ">" &
                            0
                    ) = 0,
                    COUNTIFS(
                        directReferenced!$B$2:$B$227, B2,
                        directReferenced!$E$2:$E$227, ">" &
                            0
                    ) = 0
                ),
                "Has value in previous day",
                IF(
                    AND(
                        COUNTIFS(
                            directReferenced!$B$2:$B$227, B2,
                            directReferenced!$C$2:$C$227, ">" &
                                0
                        ) > 0,
                        COUNTIFS(
                            directReferenced!$B$2:$B$227, B2,
                            directReferenced!$D$2:$D$227, ">" &
                                0
                        ) > 0,
                        COUNTIFS(
                            directReferenced!$B$2:$B$227, B2,
                            directReferenced!$E$2:$E$227, ">" &
                                0
                        ) > 0
                    ),
                    "Has value in previous day, today and tomorrow",
                    ""
                )
            )
        )
    )
)=IF(
    AND(
        COUNTIFS(
            directReferenced!$B$2:$B$227, B2,
            directReferenced!$C$2:$C$227, ">" &
                0
        ) > 0,
        COUNTIFS(
            directReferenced!$B$2:$B$227, B2,
            directReferenced!$D$2:$D$227, ">" &
                0
        ) > 0,
        COUNTIFS(
            directReferenced!$B$2:$B$227, B2,
            directReferenced!$E$2:$E$227, ">" &
                0
        ) = 0
    ),
    "Has value in previous and today",
    IF(
        AND(
            COUNTIFS(
                directReferenced!$B$2:$B$227, B2,
                directReferenced!$C$2:$C$227, ">" &
                    0
            ) = 0,
            COUNTIFS(
                directReferenced!$B$2:$B$227, B2,
                directReferenced!$D$2:$D$227, ">" &
                    0
            ) > 0,
            COUNTIFS(
                directReferenced!$B$2:$B$227, B2,
                directReferenced!$E$2:$E$227, ">" &
                    0
            ) = 0
        ),
        "Has value in today",
        IF(
            AND(
                COUNTIFS(
                    directReferenced!$B$2:$B$227, B2,
                    directReferenced!$C$2:$C$227, ">" &
                        0
                ) = 0,
                COUNTIFS(
                    directReferenced!$B$2:$B$227, B2,
                    directReferenced!$D$2:$D$227, ">" &
                        0
                ) = 0,
                COUNTIFS(
                    directReferenced!$B$2:$B$227, B2,
                    directReferenced!$E$2:$E$227, ">" &
                        0
                ) > 0
            ),
            "Has value in tomorrow",
            IF(
                AND(
                    COUNTIFS(
                        directReferenced!$B$2:$B$227, B2,
                        directReferenced!$C$2:$C$227, ">" &
                            0
                    ) > 0,
                    COUNTIFS(
                        directReferenced!$B$2:$B$227, B2,
                        directReferenced!$D$2:$D$227, ">" &
                            0
                    ) = 0,
                    COUNTIFS(
                        directReferenced!$B$2:$B$227, B2,
                        directReferenced!$E$2:$E$227, ">" &
                            0
                    ) = 0
                ),
                "Has value in previous day",
                IF(
                    AND(
                        COUNTIFS(
                            directReferenced!$B$2:$B$227, B2,
                            directReferenced!$C$2:$C$227, ">" &
                                0
                        ) > 0,
                        COUNTIFS(
                            directReferenced!$B$2:$B$227, B2,
                            directReferenced!$D$2:$D$227, ">" &
                                0
                        ) > 0,
                        COUNTIFS(
                            directReferenced!$B$2:$B$227, B2,
                            directReferenced!$E$2:$E$227, ">" &
                                0
                        ) > 0
                    ),
                    "Has value in previous day, today and tomorrow",
                    ""
                )
            )
        )
    )
)=IF(
    AND(
        COUNTIFS(
            directReferenced!$B$2:$B$227, B2,
            directReferenced!$C$2:$C$227, ">" &
                0
        ) > 0,
        COUNTIFS(
            directReferenced!$B$2:$B$227, B2,
            directReferenced!$D$2:$D$227, ">" &
                0
        ) > 0,
        COUNTIFS(
            directReferenced!$B$2:$B$227, B2,
            directReferenced!$E$2:$E$227, ">" &
                0
        ) = 0
    ),
    "Has value in previous and today",
    IF(
        AND(
            COUNTIFS(
                directReferenced!$B$2:$B$227, B2,
                directReferenced!$C$2:$C$227, ">" &
                    0
            ) = 0,
            COUNTIFS(
                directReferenced!$B$2:$B$227, B2,
                directReferenced!$D$2:$D$227, ">" &
                    0
            ) > 0,
            COUNTIFS(
                directReferenced!$B$2:$B$227, B2,
                directReferenced!$E$2:$E$227, ">" &
                    0
            ) = 0
        ),
        "Has value in today",
        IF(
            AND(
                COUNTIFS(
                    directReferenced!$B$2:$B$227, B2,
                    directReferenced!$C$2:$C$227, ">" &
                        0
                ) = 0,
                COUNTIFS(
                    directReferenced!$B$2:$B$227, B2,
                    directReferenced!$D$2:$D$227, ">" &
                        0
                ) = 0,
                COUNTIFS(
                    directReferenced!$B$2:$B$227, B2,
                    directReferenced!$E$2:$E$227, ">" &
                        0
                ) > 0
            ),
            "Has value in tomorrow",
            IF(
                AND(
                    COUNTIFS(
                        directReferenced!$B$2:$B$227, B2,
                        directReferenced!$C$2:$C$227, ">" &
                            0
                    ) > 0,
                    COUNTIFS(
                        directReferenced!$B$2:$B$227, B2,
                        directReferenced!$D$2:$D$227, ">" &
                            0
                    ) = 0,
                    COUNTIFS(
                        directReferenced!$B$2:$B$227, B2,
                        directReferenced!$E$2:$E$227, ">" &
                            0
                    ) = 0
                ),
                "Has value in previous day",
                IF(
                    AND(
                        COUNTIFS(
                            directReferenced!$B$2:$B$227, B2,
                            directReferenced!$C$2:$C$227, ">" &
                                0
                        ) > 0,
                        COUNTIFS(
                            directReferenced!$B$2:$B$227, B2,
                            directReferenced!$D$2:$D$227, ">" &
                                0
                        ) > 0,
                        COUNTIFS(
                            directReferenced!$B$2:$B$227, B2,
                            directReferenced!$E$2:$E$227, ">" &
                                0
                        ) > 0
                    ),
                    "Has value in previous day, today and tomorrow",
                    ""
                )
            )
        )
    )
)=IF(
    AND(
        COUNTIFS(
            directReferenced!$B$2:$B$227, B2,
            directReferenced!$C$2:$C$227, ">" &
                0
        ) > 0,
        COUNTIFS(
            directReferenced!$B$2:$B$227, B2,
            directReferenced!$D$2:$D$227, ">" &
                0
        ) > 0,
        COUNTIFS(
            directReferenced!$B$2:$B$227, B2,
            directReferenced!$E$2:$E$227, ">" &
                0
        ) = 0
    ),
    "Has value in previous and today",
    IF(
        AND(
            COUNTIFS(
                directReferenced!$B$2:$B$227, B2,
                directReferenced!$C$2:$C$227, ">" &
                    0
            ) = 0,
            COUNTIFS(
                directReferenced!$B$2:$B$227, B2,
                directReferenced!$D$2:$D$227, ">" &
                    0
            ) > 0,
            COUNTIFS(
                directReferenced!$B$2:$B$227, B2,
                directReferenced!$E$2:$E$227, ">" &
                    0
            ) = 0
        ),
        "Has value in today",
        IF(
            AND(
                COUNTIFS(
                    directReferenced!$B$2:$B$227, B2,
                    directReferenced!$C$2:$C$227, ">" &
                        0
                ) = 0,
                COUNTIFS(
                    directReferenced!$B$2:$B$227, B2,
                    directReferenced!$D$2:$D$227, ">" &
                        0
                ) = 0,
                COUNTIFS(
                    directReferenced!$B$2:$B$227, B2,
                    directReferenced!$E$2:$E$227, ">" &
                        0
                ) > 0
            ),
            "Has value in tomorrow",
            IF(
                AND(
                    COUNTIFS(
                        directReferenced!$B$2:$B$227, B2,
                        directReferenced!$C$2:$C$227, ">" &
                            0
                    ) > 0,
                    COUNTIFS(
                        directReferenced!$B$2:$B$227, B2,
                        directReferenced!$D$2:$D$227, ">" &
                            0
                    ) = 0,
                    COUNTIFS(
                        directReferenced!$B$2:$B$227, B2,
                        directReferenced!$E$2:$E$227, ">" &
                            0
                    ) = 0
                ),
                "Has value in previous day",
                IF(
                    AND(
                        COUNTIFS(
                            directReferenced!$B$2:$B$227, B2,
                            directReferenced!$C$2:$C$227, ">" &
                                0
                        ) > 0,
                        COUNTIFS(
                            directReferenced!$B$2:$B$227, B2,
                            directReferenced!$D$2:$D$227, ">" &
                                0
                        ) > 0,
                        COUNTIFS(
                            directReferenced!$B$2:$B$227, B2,
                            directReferenced!$E$2:$E$227, ">" &
                                0
                        ) > 0
                    ),
                    "Has value in previous day, today and tomorrow",
                    ""
                )
            )
        )
    )
)

A few defined names and I might even be able to see what is going on!

[#11]

@OliverScheurich Need one more help please for another enhancement to my exercise. I tried to play around with the formulas we have generated but in vain. What would be the formula to use in column "Condition" to generate such result

Having the following: 

sheet1

country	Condition
x	has value in 2023
y	has value in 2024
w	has no value
z	has value in 2023 and 2024

sheet2

country	year	value
w	2023xx
x	2023xx	3
y	2024xx	5
z	2023xx	1
z	2024xx	2

thanks,

[#12]

@abukapsoun 

=IF(AND(SUMPRODUCT(($A$10:$A$14=A3)*ISNUMBER(SEARCH(2023,$B$10:$B$14))*$C$10:$C$14)>0,SUMPRODUCT(($A$10:$A$14=A3)*ISNUMBER(SEARCH(2024,$B$10:$B$14))*$C$10:$C$14)>0),"has value in 2023 and 2024",IF(SUMPRODUCT(($A$10:$A$14=A3)*ISNUMBER(SEARCH(2024,$B$10:$B$14))*$C$10:$C$14)>0,"has value in 2024",IF(SUMPRODUCT(($A$10:$A$14=A3)*ISNUMBER(SEARCH(2023,$B$10:$B$14))*$C$10:$C$14)>0,"has value in 2023","has no value")))

This formula returns the intended result in my sheet.

[#13]

thank you thank you thank you.. it worked. will apply it on the bigger dataset 

[#14]

@OliverScheurich I have a new scenario and thought of asking you to find the formula of the below condition if you don't mind. 

sheet1

Country	Period	Split Value	Condition
x	P1	10
y	P1	20	if value of all y (P1+P2+P3) > sheet 2 Total value of y then split value is incorrect
y	P2	40
y	P3	20

sheet2

Country	TotalValue
x	50
y	60

thank you again and again

[#15]

@abukapsoun 

=IF(SUMPRODUCT(($A$3:$A$8=A3)*$C$3:$C$8)>INDEX($B$12:$B$14,MATCH(A3,$A$12:$A$14,0)),"Split value is incorrect","")

You can try this formula which returns the expected result in my understanding.

[#16]

Hi @OliverScheurich 

I am back :)

I got one more if you don't mind, and thank you in advance.

Name	Country	Result
AA	XX	If AA has XX in column country, return XX
AA	Others	If AA has XX in column country, return XX
B	XX	If BB has XX in column country, return XX
B	Others	If BB has XX in column country, return XX

[#17]

@abukapsoun 

You are welcome to ask as many questions as you want to of course. You can as well start a new discussion for a new question.

With the sample data i'm unsure what you want to achieve.

Do you actually want to return "If AA has XX in column country, return XX" for both combinations of Name and Country? Or is there another intended result for AA and Others?

[#18]

Thank you @OliverScheurich. I got it addressed through a powerBI DAX formula with the support of powerBI community.

Appreciated. Next question, I will have it posted in a new thread. :)

[#19]

@abukapsoun 

I am still puzzled by your

= _xlfn.MAP(pointer, IdentTimingλ)

It seems as if you do not have access to the Lambda helper function MAP.

The LET function was simply the definition of the Lambda function 'IdentTimingλ' and is best viewed using the Advanced Formula Environment.  Hopefully the AFE will replace the formula bar in due course though there might well be howls of protest were the formula bar removed now.

Since named Lambda functions use defined names, the formula should also be viewable using Name Manager.  Moving to the BI environment should be unnecessary, though if you have a lot of slicing and dicing to do with linked data-tables it would make sense to do your work there.