Forum Discussion

Copper Contributor

Feb 08, 2023

Solved

Returning a value based on multiple columns

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 Poin...

excel

Formulas and Functions

OliverScheurich

Feb 08, 2023

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

Sheet2:

OliverScheurich

Gold Contributor

Feb 08, 2023

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

Sheet2:

abukapsoun
Copper Contributor
Feb 08, 2023
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!
- OliverScheurich
  Gold Contributor
  Feb 08, 2023
  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 2:
  - abukapsoun
    Copper Contributor
    Feb 09, 2023
    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"