Feb 08 2023 06:47 AM
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.
Feb 08 2023 07:32 AM
Solution=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:
Feb 08 2023 11:37 AM
Thank you very much! I cannot thank you enough, it is working like charm! Still need one more support please. For the "not found", I need to put make it two category based on certain string found in pointer cell.
for example, if B=0 and C=0 and D=0 and A2 starts with "xxx" then "Not Found1", Else "not found"
thank you!
Feb 08 2023 02:12 PM
This is 365; a worksheet formula that looks more like a code snippet.
Worksheet formula
= MAP(pointer, IdentTimingλ)
where IdentTimingλ(ptr) is
= LAMBDA(ptr,
LET(
row, XLOOKUP(ptr, tablePtr, table, 0),
hdr, FILTER(timing, row>0, ""),
IF(@hdr<>"", "Has value in " & TEXTJOIN(", ", , hdr), "Not found")
)
)
Feb 08 2023 02:27 PM
=IFNA(IF(AND(INDEX(sheet2!$B$2:$B$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))>0,INDEX(sheet2!$C$2:$C$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))=0,INDEX(sheet2!$D$2:$D$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))=0),"Has value in previous day",IF(AND(INDEX(sheet2!$B$2:$B$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))=0,INDEX(sheet2!$C$2:$C$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))>0,INDEX(sheet2!$D$2:$D$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))=0),"Has value in today",IF(AND(INDEX(sheet2!$B$2:$B$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))>0,INDEX(sheet2!$C$2:$C$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))>0,INDEX(sheet2!$D$2:$D$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))>0),"Has value in previous day, today and tomorrow",IF(AND(LEFT(sheet1!A2,3)="xxx",INDEX(sheet2!$B$2:$B$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))=0,INDEX(sheet2!$C$2:$C$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))=0,INDEX(sheet2!$D$2:$D$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))=0),"Not Found1","")))),"Not Found")
You are welcome. You can try this formula.
Sheet 1:
Sheet 2:
Feb 08 2023 10:44 PM - edited Feb 08 2023 11:53 PM
I am having a small situation when I am replicating it to my dataset, in my sheet2 there is no xxx7, it is only available in sheet1. I think because of that, the result, I am getting is always "Not found" rather than "Not found1".
Would the formula be different if xxx7 is not present in sheet2?
So the condition would be, if A2 in sheet1 starts with xxx and A2 is not available in shee2 column A then "Not found1"
Feb 09 2023 12:24 AM
@OliverScheurich Not sure what this could be, but I am not able to pin point the issue. even if I add a dummy entry for xxx7 in sheet2, I still don't get "Not Found1"
I have my code just in case you can help me, in fact you can see I have added few conditions, but just repetition
sheet1 = NS finalv3
sheet2 = OI finalv3
B=M
C=N
D=Q
=IFNA(IF(AND(INDEX('OI Finalv3'!$M$2:$M$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))>0,INDEX('OI Finalv3'!$N$2:$N$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))=0,INDEX('OI Finalv3'!$Q$2:$Q$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))=0),"Has value in previous day",IF(AND(INDEX('OI Finalv3'!$M$2:$M$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))=0,INDEX('OI Finalv3'!$N$2:$N$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))>0,INDEX('OI Finalv3'!$Q$2:$Q$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))=0),"Has value in today",IF(AND(INDEX('OI Finalv3'!$M$2:$M$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))=0,INDEX('OI Finalv3'!$N$2:$N$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))=0,INDEX('OI Finalv3'!$Q$2:$Q$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))>0),"Has value in tomorrow",IF(AND(INDEX('OI Finalv3'!$M$2:$M$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))>0,INDEX('OI Finalv3'!$N$2:$N$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))>0,INDEX('OI Finalv3'!$Q$2:$Q$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))=0),"Has value in previous and today",IF(AND(INDEX('OI Finalv3'!$M$2:$M$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$M$2:$M$227,0))>0,INDEX('OI Finalv3'!$N$2:$N$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))>0,INDEX('OI Finalv3'!$Q$2:$Q$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))>0),"Has value in previous day, today and tomorrow",IF(AND(LEFT('NS Finalv3'!H2,3)="OIF",INDEX('OI Finalv3'!$M$2:$M$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))=0,INDEX('OI Finalv3'!$N$2:$N$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))=0,INDEX('OI Finalv3'!$Q$2:$Q$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))=0),"Not Found1","")))))),"Not Found")
Feb 09 2023 04:16 AM
If A2 isn't present in sheet2 and A2 starts with "xxx" you can try this formula:
=IFNA(IF(AND(INDEX(sheet2!$B$2:$B$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))>0,INDEX(sheet2!$C$2:$C$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))=0,INDEX(sheet2!$D$2:$D$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))=0),"Has value in previous day",IF(AND(INDEX(sheet2!$B$2:$B$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))=0,INDEX(sheet2!$C$2:$C$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))>0,INDEX(sheet2!$D$2:$D$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))=0),"Has value in today",IF(AND(INDEX(sheet2!$B$2:$B$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))>0,INDEX(sheet2!$C$2:$C$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))>0,INDEX(sheet2!$D$2:$D$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))>0),"Has value in previous day, today and tomorrow",""))),IF(LEFT(sheet1!A2,3)="xxx","Not Found1","Not Found"))
Feb 09 2023 06:04 AM
THANK YOU VERY MUCH! it worked. @OliverScheurich
Feb 09 2023 01:42 PM
This is the formula where "Not Found1" is returned if the pointer isn't found in sheet "OI finalv3" and if the pointer starts with "xxx".
=IFNA(IF(AND(INDEX('OI Finalv3'!$M$2:$M$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))>0,INDEX('OI Finalv3'!$N$2:$N$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))=0,INDEX('OI Finalv3'!$Q$2:$Q$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))=0),"Has value in previous day",IF(AND(INDEX('OI Finalv3'!$M$2:$M$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))=0,INDEX('OI Finalv3'!$N$2:$N$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))>0,INDEX('OI Finalv3'!$Q$2:$Q$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))=0),"Has value in today",IF(AND(INDEX('OI Finalv3'!$M$2:$M$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))=0,INDEX('OI Finalv3'!$N$2:$N$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))=0,INDEX('OI Finalv3'!$Q$2:$Q$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))>0),"Has value in tomorrow",IF(AND(INDEX('OI Finalv3'!$M$2:$M$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))>0,INDEX('OI Finalv3'!$N$2:$N$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))>0,INDEX('OI Finalv3'!$Q$2:$Q$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))=0),"Has value in previous and today",IF(AND(INDEX('OI Finalv3'!$M$2:$M$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))>0,INDEX('OI Finalv3'!$N$2:$N$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))>0,INDEX('OI Finalv3'!$Q$2:$Q$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))>0),"Has value in previous day, today and tomorrow",""))))),IF(LEFT('NS Finalv3'!H2,3)="xxx","Not Found1","Not Found"))
Feb 12 2023 01:22 AM - edited Feb 12 2023 03:07 AM
I bumped into the following issue, and would like to have your advice.
1)
Column H | Column N | Column Q |
x | 0 | |
x1 | 10 | |
x2 | 0 | |
x2 | 15 |
now according to the formula, i am getting "OI no longer in sales pipeline", which is not really true since it has a value in another row. How can I fix that?
=IFNA(IF(AND(INDEX('OI Finalv3'!$N$2:$N$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))=0,INDEX('OI Finalv3'!$Q$2:$Q$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))=0),"OI no longer in sales pipeline",IF(AND(INDEX('OI Finalv3'!$N$2:$N$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))>0,INDEX('OI Finalv3'!$Q$2:$Q$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))=0),"OI in Sales LE (Secure+Risk)",IF(AND(INDEX('OI Finalv3'!$N$2:$N$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))=0,INDEX('OI Finalv3'!$Q$2:$Q$227,MATCH('NS Finalv3'!H2,'OI Finalv3'!$H$2:$H$227,0))>0),"OI planned in Upside & ROP (NonLE)",""))),IF(LEFT('NS Finalv3'!H2,3)="OIF","No associated sales in sCRM","OBL line item"))
2)
Another thing please, I am trying hard but not able to find it. What would be the formula if we are only assessing 1 column?
Sheet1
pointer | value |
x1 | 10 |
x2 | 15 |
x3 | 20 |
Sheet2
has value in sheet1? | |
x1 | |
x2 | |
x5 | |
3) In that piece of formula, how can I add another check, for if H2="yyy" then "Not found2"
,IF(LEFT('NS Finalv3'!H2,3)="xxx","Not Found1","Not Found"))
Sorry for being a newbie and only making things difficult.
Thank you
Feb 12 2023 02:51 AM
Thank God, I no longer use traditional spreadsheet formulas!
Feb 12 2023 03:40 AM
I fully appreciate that you have a job to do and @OliverScheurich is producing solutions that will help you. No way would I wish to undermine that. It doesn't stop me from being thankful that I no longer need to face such formula; I never was sufficiently accurate to see it though anyway!
Feb 12 2023 03:44 AM
Feb 12 2023 03:48 AM
Feb 12 2023 05:16 AM
In the attached file you can find formulas for questions 2 and 3. For question 1 i'm unsure what the expected results are. Can you add the expected results in the "NS Finalv3" sheet maybe in column P next to the results of the formula?
Feb 12 2023 06:02 AM
Thank you very much @OliverScheurich I hope I am not bothering you with my repetitive questions :)
for question 1), let me go back to the initial example we had as our basis,
sheet1
Pointer | Where is it? |
x1 | |
x2 | has value in previous day and today |
x3 | |
x4 |
sheet2
previous day | today | tomorrow | |
x1 | |||
x2 | 15 | 0 | |
x2 | 0 | 15 | |
x3 |
the code used previously
=IFNA(IF(AND(INDEX(sheet2!$B$2:$B$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))>0,INDEX(sheet2!$C$2:$C$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))=0,INDEX(sheet2!$D$2:$D$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))=0),"Has value in previous day",IF(AND(INDEX(sheet2!$B$2:$B$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))=0,INDEX(sheet2!$C$2:$C$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))>0,INDEX(sheet2!$D$2:$D$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))=0),"Has value in today",IF(AND(INDEX(sheet2!$B$2:$B$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))>0,INDEX(sheet2!$C$2:$C$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))>0,INDEX(sheet2!$D$2:$D$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))>0),"Has value in previous day, today and tomorrow",IF(AND(LEFT(sheet1!A2,3)="xxx",INDEX(sheet2!$B$2:$B$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))=0,INDEX(sheet2!$C$2:$C$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))=0,INDEX(sheet2!$D$2:$D$5,MATCH(sheet1!A2,sheet2!$A$2:$A$5,0))=0),"Not Found1","")))),"Not Found")
Thank you.
Feb 12 2023 06:52 AM
In this situation i'd combine the data in sheet2 into a table that shows the sums for each pointer in one row.
For example i'd transfer this:
sheet2
previous day | today | tomorrow | |
x1 | |||
x2 | 15 | 0 | |
x2 | 0 | 15 | |
x3
|
Into this:
sheet2
previous day | today | tomorrow | |
x1 | |||
x2 | 15 | 15 | |
x3 | |||
|
This would simplify the task especially if e.g. pointer x2 appears three, four or more times in sheet2.
Feb 12 2023 07:18 AM - edited Feb 12 2023 07:18 AM
@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 ?
Feb 08 2023 07:32 AM
Solution=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: