Hello Exel team,
I hope everyone has been doing great; it's been a while. I'm back today, and I'm seeking help. As you can see, I have manual input data on columns [B, C], and I have a table called CATAG where I have partial tet from column C items. I have been trying, and as you can see, all the yellow highlighted columns to return the table header as it should be in the D column highlighted in green, with no luck.
The idea is to have the formula loop all the inputs in column C and search through all the table content, match partial of the text in the table with the full text in column C, and return in column D the header name and if its not found return " - ", like what the example have in column D " I did entered the matching manual there just for the example"
I appreciate all your time and help.
Thank you
Batata
I hope everyone has been doing great; it's been a while. I'm back today, and I'm seeking help. As you can see, I have manual input data on columns [B, C], and I have a table called CATAG where I have partial tet from column C items. I have been trying, and as you can see, all the yellow highlighted columns to return the table header as it should be in the D column highlighted in green, with no luck.
The idea is to have the formula loop all the inputs in column C and search through all the table content, match partial of the text in the table with the full text in column C, and return in column D the header name and if its not found return " - ", like what the example have in column D " I did entered the matching manual there just for the example"
I appreciate all your time and help.
Thank you
Batata
| New Data.xlsx | |||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |||
| 1 | |||||||||||||||||||||
| 2 | Amount | Description | Return | Return | Return | Return | Return | Return | Return | Return | Return | Return | Return | ||||||||
| 3 | -194.44 | eBay O*07-09272-79640 408-3766151 CA 10/28 | Online Orders | #N/A | #N/A | #VALUE! | #N/A | #N/A | #N/A | #VALUE! | #N/A | #NUM! | #NUM! | ||||||||
| 4 | 146.77 | GEICO *AUTO 800-000-0000 DC 10/28 | Office | #N/A | #N/A | #VALUE! | #N/A | #N/A | #N/A | #VALUE! | #N/A | #NUM! | #NUM! | ||||||||
| 5 | -19.47 | GOOGLE *YouTubePremi g.co/helppay# CA 10/24 | Subsecribtion | #N/A | #N/A | #VALUE! | #N/A | #N/A | #N/A | #VALUE! | #N/A | #NUM! | #NUM! | ||||||||
| 6 | -12.79 | WHOLEFDS POA #10 1700 359323 10/23 | - | #N/A | #N/A | #VALUE! | #N/A | #N/A | #N/A | #VALUE! | #N/A | #NUM! | #NUM! | Subsecribtion | office | Energy | Online Orders | ||||
| 7 | -48.92 | SPLASH #10 1700 35124681 10/23 | - | #N/A | #N/A | #VALUE! | #N/A | #N/A | #N/A | #VALUE! | #N/A | #NUM! | #NUM! | NETFLIX.COM | Experian | Gexa | Ebay | ||||
| 8 | -120.99 | GEXA ENERGY 866-00-0000 10/22 | Energy | #N/A | #N/A | #VALUE! | #N/A | #N/A | #N/A | #VALUE! | #N/A | #NUM! | #NUM! | *YouTube | Domain.com | MMMS.com | amazon.com | ||||
| 9 | -400 | ORIG CO NAME:GOOGLE STORE ORIG DESC | GOOGLE STORE | #N/A | #N/A | #VALUE! | #N/A | #N/A | #N/A | #VALUE! | #N/A | #NUM! | #NUM! | GEICO | UBER | ||||||
| 10 | -139.97 | UBER EATS HELP.UBER.COM CA 10/17 | Online Orders | #N/A | #N/A | #VALUE! | #N/A | #N/A | #N/A | #VALUE! | #N/A | #NUM! | #NUM! | GOOGLE STORE | |||||||
| 11 | Experian* Credit Repor 479-0000000 CA 10/16 | Office | #N/A | #N/A | #VALUE! | #N/A | #N/A | #N/A | #VALUE! | #N/A | #NUM! | #NUM! | |||||||||
| 12 | -1073.21 | ORIG CO NAME:MMMS.COM ORIG DESC | Energy | #N/A | #N/A | #VALUE! | #N/A | #N/A | #N/A | #VALUE! | #N/A | #NUM! | #NUM! | ||||||||
| 13 | -5.32 | EIG*Domain.com 866-0000000 MA 10/11 | Office | #N/A | #N/A | #VALUE! | #N/A | #N/A | #N/A | #VALUE! | #N/A | #NUM! | #NUM! | ||||||||
| 14 | -21.88 | NETFLIX.COM NETFLIX.COM CA 10/27 | Subsecribtion | #N/A | #N/A | #VALUE! | #N/A | #N/A | #N/A | #VALUE! | #N/A | #NUM! | #NUM! | ||||||||
| 15 | |||||||||||||||||||||
Sheet1 | |||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E3:E14 | E3 | =INDEX(CATAG,1,MATCH(1,--(ISNUMBER(SEARCH(C3,CATAG))),0)) |
| F3:F14 | F3 | =INDEX(CATAG,1,MATCH(1,--(ISNUMBER(SEARCH(C3,CATAG))),0)) |
| G3:G14 | G3 | =INDEX(CATAG,MATCH(MIN(IF(SEARCH(C3,CATAG)>0,SEARCH(C3,CATAG))),IF(SEARCH(C3,CATAG)>0,SEARCH(C3,CATAG)),0),1) |
| H3:H14 | H3 | =INDEX(CATAG,MATCH(C3,CATAG,0),1) |
| I3:I14 | I3 | =INDEX(CATAG,MATCH(C3,TRANSPOSE(CATAG),0),1) |
| J3:J14 | J3 | =INDEX(CATAG,1,MATCH(TRUE,SEARCH(C3,CATAG)>0,0)) |
| K3:K14 | K3 | =INDEX(CATAG,1,MATCH(MIN(IF(SEARCH("*"&C3&"*",CATAG)>0,SEARCH("*"&C3&"*",CATAG))),IF(SEARCH("*"&C3&"*",CATAG)>0,SEARCH("*"&C3&"*",CATAG)),0)) |
| L3:L14 | L3 | =INDEX(CATAG,1,MATCH(TRUE,ISNUMBER(SEARCH(C3,CATAG)),0)) |
| M3:M14 | M3 | =INDEX(CATAG,1,AGGREGATE(15,6,COLUMN(CATAG)/((CATAG<>"")*(COUNTIF(CATAG,C3)>0)),1)) |
| N3:N14 | N3 | =INDEX(CATAG,1,AGGREGATE(15,6,COLUMN(CATAG)/(ISNUMBER(SEARCH(C3,CATAG))),1)) |
| Press CTRL+SHIFT+ENTER to enter array formulas. | ||
