I have 2 tables. Desired output should contain only those rows from source table which has `no´ in the entry column & the name is present in reference table. I´m getting an incorrect result. I´m sure I´m making a mistake & I have to use dynamic array to get the desired output. Can the experts in this forum help please?
Source table:
Reference table:
Desired output:
Incorrect output:
Source table:
| excel problems.xlsx | ||||
|---|---|---|---|---|
| B | C | |||
| 2 | name | entry | ||
| 3 | dfds | 1 | ||
| 4 | gfh | no | ||
| 5 | uir | 7 | ||
| 6 | qewr | 8.4 | ||
| 7 | xcvz | no | ||
| 8 | cmvn | 2.55 | ||
| 9 | dhgdh | no | ||
| 10 | teywu | 3 | ||
| 11 | sdf | 4 | ||
| 12 | gd | no | ||
| 13 | hkfi | no | ||
Sheet5 | ||||
Reference table:
| excel problems.xlsx | |||
|---|---|---|---|
| E | |||
| 2 | name | ||
| 3 | dfds | ||
| 4 | qewr | ||
| 5 | xcvz | ||
| 6 | cmvn | ||
| 7 | dhgdh | ||
| 8 | teywu | ||
| 9 | sdf | ||
| 10 | gd | ||
Sheet5 | |||
Desired output:
| excel problems.xlsx | ||||
|---|---|---|---|---|
| G | H | |||
| 2 | name | entry | ||
| 3 | xcvz | no | ||
| 4 | dhgdh | no | ||
| 5 | gd | no | ||
Sheet5 | ||||
Incorrect output:
| excel problems.xlsx | ||||
|---|---|---|---|---|
| K | L | |||
| 2 | name | entry | ||
| 3 | gfh | no | ||
| 4 | xcvz | no | ||
| 5 | dhgdh | no | ||
| 6 | gd | no | ||
| 7 | hkfi | no | ||
Sheet5 | ||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| K3:L7 | K3 | =LET( f,FILTER(Table2,(Table2[entry]="no")*(NOT(ISERROR(XLOOKUP(Table2[@name],Table3[name],Table3[name]))))), f) |
| Dynamic array formulas. | ||
