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:
=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:
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"
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"))
THANK YOU VERY MUCH! it worked. OliverScheurich
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")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"))
Thank God, I no longer use traditional spreadsheet formulas!
