SOLVED

Returning a value based on multiple columns

Copper Contributor

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

PointerPrevious daytodaytomorrow 
X11000 
X2050 
X5151515 

 

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

PointerWhere is it?
X1Has value in previous day
X2Has value in today
X3Not Found
X4Not Found
X5Has 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.

37 Replies
best response confirmed by Grahmfs13 (Microsoft)
Solution

@abukapsoun 

=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

sheet1.JPG

 

Sheet2:

sheet2.JPG

 

@OliverScheurich 

 

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! 

@abukapsoun 

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")
    )
  )

image.png

 

@abukapsoun 

=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 1.JPG

 

Sheet 2:

sheet 2.JPG

 

 

Hi @OliverScheurich 

 

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"

 

@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")

 

@abukapsoun 

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"))

 

sheet one.JPGsheet two.JPG

THANK YOU VERY MUCH! it worked. @OliverScheurich 

@abukapsoun 

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"))

 

not found1.JPG

Hi @OliverScheurich 

 

I bumped into the following issue, and would like to have your advice. 

 

1)

Column HColumn NColumn Q
x0 
x110 
x20 
x215 

 

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

pointervalue
x110
x215
x320

 

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

@OliverScheurich 

Thank God, I no longer use traditional spreadsheet formulas!

This is not helping :)

@abukapsoun 

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!

Thank you Peter, by the way I tried earlier to go through your solution, but to be frank, it was too advanced for my brain to understand. If you have the patience and time to walk me through so I can apply it to my exercise, it would be a great learning for me.
It says Update to Windows 11 but, say my machine doesn't meet the requirments ? I don't know what I need ?

@abukapsoun 

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? 

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

PointerWhere is it?
x1 
x2has value in previous day and today
x3 
x4 

 

sheet2

 previous daytodaytomorrow
x1   
x2150 
x2015 
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. 

@abukapsoun 

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 daytodaytomorrow
x1   
x2150 
x2015 

x3

 

 

Into this:

sheet2

 previous daytodaytomorrow
x1   
x21515 
x3   
   

 

 

 

This would simplify the task especially if e.g. pointer x2 appears three, four or more times in 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 ?

1 best response

Accepted Solutions
best response confirmed by Grahmfs13 (Microsoft)
Solution

@abukapsoun 

=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

sheet1.JPG

 

Sheet2:

sheet2.JPG

 

View solution in original post