Forum Discussion
Returning a value based on multiple columns
=IFNA(IF(AND(INDEX(sheet2!$B$2:$B$4,MATCH(sheet1!A2,sheet2!$A$2:$A$4,0))>0,INDEX(sheet2!$C$2:$C$4,MATCH(sheet1!A2,sheet2!$A$2:$A$4,0))=0,INDEX(sheet2!$D$2:$D$4,MATCH(sheet1!A2,sheet2!$A$2:$A$4,0))=0),"Has value in previous day",IF(AND(INDEX(sheet2!$B$2:$B$4,MATCH(sheet1!A2,sheet2!$A$2:$A$4,0))=0,INDEX(sheet2!$C$2:$C$4,MATCH(sheet1!A2,sheet2!$A$2:$A$4,0))>0,INDEX(sheet2!$D$2:$D$4,MATCH(sheet1!A2,sheet2!$A$2:$A$4,0))=0),"Has value in today",IF(AND(INDEX(sheet2!$B$2:$B$4,MATCH(sheet1!A2,sheet2!$A$2:$A$4,0))>0,INDEX(sheet2!$C$2:$C$4,MATCH(sheet1!A2,sheet2!$A$2:$A$4,0))>0,INDEX(sheet2!$D$2:$D$4,MATCH(sheet1!A2,sheet2!$A$2:$A$4,0))>0),"Has value in previous day, today and tomorrow",""))),"Not Found")A laborious solution would be this nested IF formula.
Sheet1
Sheet2:
OliverScheurich That would be difficult to transform the table, since this repetition is due to some other columns that exists as well. Can we maybe count the number of occurrences in "previous day" and "today" column? For example, we say if count of x2 in previous day =>1 andif count of occurence of x2 in today =>1 then has value in previous day and today ?
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","")))))
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.
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. 🙂
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?
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 =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.
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
thank you thank you thank you.. it worked. will apply it on the bigger dataset
=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.
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,
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!
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 🙂 )
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"))))))
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"))
