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