I am trying to use the xlookup function with multiple criteria and partial text match. The formula is in F11 on the IR tab. I want to return the value in column H is the account numbers match, and if Column F on the OAR tab contains "Max 10%", but my formula is not working. Does anyone know what's wrong with it?
| Formula.xlsx | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 1 | |||||||||||
| 2 | |||||||||||
| 3 | |||||||||||
| 4 | |||||||||||
| 5 | |||||||||||
| 6 | OAR TAB | ||||||||||
| 7 | |||||||||||
| 8 | |||||||||||
| 9 | |||||||||||
| 10 | Session ID | Request ID | Session Label | Session Type | Account Number | Violation Description | Security | Override Status | Request Status | ||
| 11 | 42 | Cascade | 123 | SecurityLimit Restriction Max 10% Cash -- before: 4888.85 after: 16237.20 max: 12711.23 | XYZ | Approved with Note | Approved | ||||
OAR | |||||||||||
| Formula.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | |||||||||
| 2 | |||||||||
| 3 | |||||||||
| 4 | |||||||||
| 5 | |||||||||
| 6 | |||||||||
| 7 | |||||||||
| 8 | IR TAB | ||||||||
| 9 | |||||||||
| 10 | Id | Account | Sleeve | Alert Type | Description | Override Status | Request Status | ||
| 11 | 123 | Restriction Violation | #N/A | ||||||
| 12 | XLOOKUP(1,(OAR!$E$11:$E$100000=B11)*(OAR!$F$11:$F$100000="*Max 10%*"),OAR!$H$11:$H$100000) | ||||||||
IR | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F11 | F11 | =XLOOKUP(1,(OAR!$E$11:$E$100000=B11)*(OAR!$F$11:$F$100000="*Max 10%*"),OAR!$H$11:$H$100000) |
