Forum Discussion
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...
=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:
Thank you PeterBartholomew1, Yes I have O365 and I am interested to look into that new area. But one thing, When I opened you earlier excel sheet, I could only find that piece of code "= _xlfn.MAP(pointer, IdentTimingλ)", I am not able to find where you have inserted the LET function
The formula
= MAP(pointer, IdentTimingλ)is in cell C4 of Sheet1. It looks as if the sheet has been opened in legacy Excel and the returned text shows that the MAP function is unknown. Is the Lambda function itself visible within Name Manager or has that been scrambled too?