Forum Discussion
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
| Pointer | Previous day | today | tomorrow | |
| X1 | 10 | 0 | 0 | |
| X2 | 0 | 5 | 0 | |
| X5 | 15 | 15 | 15 |
I want to create a new column in sheet1 and populated with a text result after doing a search in sheet2 like the following:
| Pointer | Where is it? |
| X1 | Has value in previous day |
| X2 | Has value in today |
| X3 | Not Found |
| X4 | Not Found |
| X5 | Has 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.
=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:
37 Replies
- It says Update to Windows 11 but, say my machine doesn't meet the requirments ? I don't know what I need ?
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") ) )=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 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!
=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:
