Wishmaster89
Board Regular
- Joined
- Jan 10, 2022
- Messages
- 77
- Office Version
- 2021
- 2019
- 2016
- Platform
- Windows
- MacOS
Hi All
I'm trying to combine LET with IF but i'm having some trouble. It seems like when i get to the false bit of the IF, the LET statement is out of scope so i can't do a name value 2/name 2 calculation.
In column E, I currently have xlookup and if it doesn't return 0 it performs the original xlookup, which is why i want to use LET to avoid writing out the xlookup twice
This is what i have tried so far , the second function i refer to below is XLOOKUP(A6&C6,$M$2:$M$6&$O$2:$O$6,$Q$2:$Q$6))),XLOOKUP(A6&C6,$M$2:$M$6&$O$2:$O$6,$Q$2:$Q$6)
I'm trying to combine LET with IF but i'm having some trouble. It seems like when i get to the false bit of the IF, the LET statement is out of scope so i can't do a name value 2/name 2 calculation.
In column E, I currently have xlookup and if it doesn't return 0 it performs the original xlookup, which is why i want to use LET to avoid writing out the xlookup twice
This is what i have tried so far , the second function i refer to below is XLOOKUP(A6&C6,$M$2:$M$6&$O$2:$O$6,$Q$2:$Q$6))),XLOOKUP(A6&C6,$M$2:$M$6&$O$2:$O$6,$Q$2:$Q$6)
Excel Formula:
=if(let(a,xlookup(A2&B2,$M$2:$M$6&$N$2:N6,$Q$2:$Q$6))<>0,a,[B]b(2nd function)[/B],
Book2 | |||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | |||
1 | ID | Start date | Course ID | Course | Grade | ID | Start date | Course ID | Course | Grade | |||||||||
2 | 1 | 01/03/2019 | 33 | Maths | B | 2 | 15/10/2020 | 55 | English | F | |||||||||
3 | 2 | 15/10/2020 | 55 | English | F | 4 | 28/09/2021 | 75 | Geography | A | |||||||||
4 | 3 | 07/10/2021 | 66 | IT | U | 3 | 03/06/2018 | 66 | IT | U | |||||||||
5 | 4 | 28/09/2021 | 75 | Geography | A | 1 | 01/03/2019 | 33 | Maths | B | |||||||||
6 | 7 | 01/03/2021 | 12 | Sports | FALSE | 7 | 01/03/2021 | 12 | Sports | ||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2:E6 | E2 | =IFERROR(IF(XLOOKUP(A2&B2,$M$2:$M$6&$N$2:$N$6,$Q$2:$Q$6)<>0,XLOOKUP(A2&B2,$M$2:$M$6&$N$2:$N$6,$Q$2:$Q$6,XLOOKUP(A2&C2,$M$2:$M$6&$O$2:$O$6,$Q$2:$Q$6))),XLOOKUP(A2&C2,$M$2:$M$6&$O$2:$O$6,$Q$2:$Q$6)) |