I'm trying find a value within a range using choose formula, but the workbook is big and choose is taking so much memory that it slowing the workbook a lot. I tried to find a solution for it, but so far I haven't found anything that might work yet. I was thinking Index and match, but I'd need the row match to find the description within the index area and match is a one way street formula. Pretty much what I'm looking for is. cell E3-G6 to give me the description from column A, based on the information in column I-N. ex. if AK answer (M3) is "Yes", therefore "Bonus Addback" is showing in AK terminology (N3) which it should translate that description to Arkansas (G3). I've been using choose to find "Yes" to give me the description and manually adjusting the formula for every state in the workbook, but it seems to be a very time consuming on top of the workbook being super slow as it is. Any help could be highly appreciated?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Index-match or Xlookup question
- Thread starter Oras78
- Start date
Hi,
if you see rows 6 and 7 have the original formula I'm using right now, but the problem is that the description can be anywhere within the table range and a simple xlookup or index-match can't do that. I tried using a helper in row P, but obviously it only works if you have one description.
I'm trying to use XL2BB but for some odd reason I can't paste it here. I looked at some other threads, but I didn't see a solution for it. so the best I can do is to use screenshots sorry about using this approach!
if you see rows 6 and 7 have the original formula I'm using right now, but the problem is that the description can be anywhere within the table range and a simple xlookup or index-match can't do that. I tried using a helper in row P, but obviously it only works if you have one description.
I'm trying to use XL2BB but for some odd reason I can't paste it here. I looked at some other threads, but I didn't see a solution for it. so the best I can do is to use screenshots sorry about using this approach!
Attachments
Upvote
0
After looking at various threads I found a solution to XL2BB.
Just to rephrase my help request, I need a formula that can search the description in column "A" within the raw data in columns I-N based on its applicable state. the problem I'm facing is that the descriptions ex. "AL Terminology" gets shifted every often, as new data is entered, therefore I would need to rearrange my formulas whenever that happens. I tried to use a helper but it's not working when there are two descriptions in the same row. any help would be really appreciated as I have a deadline approaching faster than expected.
Just to rephrase my help request, I need a formula that can search the description in column "A" within the raw data in columns I-N based on its applicable state. the problem I'm facing is that the descriptions ex. "AL Terminology" gets shifted every often, as new data is entered, therefore I would need to rearrange my formulas whenever that happens. I tried to use a helper but it's not working when there are two descriptions in the same row. any help would be really appreciated as I have a deadline approaching faster than expected.
| Mr Excel Testing.xlsx | ||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | E | F | G | H | I | J | K | L | M | N | O | P | ||||||
| 1 | DESCRIPTION | Alabama | Arizona | Arkansas | AL | AL | AZ | AZ | AK | AK | ||||||||
| 2 | AL Terminology | AZ Terminology | AK Terminology | Original Formula | AL Answer | AL Terminology | AZ Answer | AZ Terminology | AK Answer | AK Terminology | Helper | |||||||
| 3 | Bonus Addback | X | X | Yes | Bonus Addback | No | Yes | Bonus Addback | Bonus Addback | |||||||||
| 4 | Bonus Addback - QIP | X | X | Yes | Bonus Addback - QIP | Yes | Bonus Addback - QIP | No | Bonus Addback - QIP | |||||||||
| 5 | Bonus Subtraction | X | Yes | Bonus Subtraction | No | No | Bonus Subtraction | |||||||||||
| 6 | AL Bonus 2020 Subtraction | X | X | Yes | AL Bonus 2020 Subtraction | Yes | AZ Bonus 2020 Subtraction | No | Bonus 2020 Subtraction | |||||||||
| 7 | AZ Bonus 2020 Subtraction | X | X | No | No | No | ||||||||||||
Sheet1 | ||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E2 | E2 | ="AL"&" Terminology" |
| F2 | F2 | ="AZ"&" Terminology" |
| G2 | G2 | ="AK"&" Terminology" |
| E3:G5 | E3 | =IF(XLOOKUP($A3,$P$3:$P$7,XLOOKUP(E$2,$I$2:$N$2,$I$3:$N$7))=$A3,"X","") |
| E6:E7 | E6 | =IF(IFERROR(VLOOKUP($A6,CHOOSE({1,2},J$3:J$8,I$3:I$8),2,0),"")="Yes","X","") |
| F6:F7 | F6 | =IF(IFERROR(VLOOKUP($A6,CHOOSE({1,2},L$3:L$8,K$3:K$8),2,0),"")="Yes","X","") |
| G6:G7 | G6 | =XLOOKUP($A6,$P$3:$P$7,XLOOKUP(G$2,$I$2:$N$2,$I$3:$N$7),"") |
| I2,M2,K2 | I2 | =I1&" Answer" |
| J2,N2,L2 | J2 | =J1&" Terminology" |
| J3:J5,N3:N7,L3:L5 | J3 | =IF(AND(I3="Yes",$A3<>""),$P3,"") |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| F5 | Cell Value | duplicates | text | NO |
| P3:P7 | Cell Value | duplicates | text | NO |
| N3:N7,J3:J7,L3:L7 | Cell Value | duplicates | text | NO |
| A3:A7 | Cell Value | duplicates | text | NO |
Upvote
0
