MannStewart
New Member
- Joined
- Oct 5, 2019
- Messages
- 14
Hi
Can anyone guide me in how to tweak my MATCH function?
Issue is the MATCH function would overlook the source data when the source file provider (for some silly reason) randomly attaches some random ASCII characters to some of the cells contents, for eg. if a DOT or a COMMA is attached to the letters, the MATCH will return nothing or not found when the item is there in the source. The MATCH function just works perfectly fine when the source data comes clean with no ASCII characters attached, and I have no way to control how the source organises the data contents.
Say, one of my symbol is "TYPH02" which corresponds to looked for data item in source file is TYPH02 that INDEXES for 2020_06_22 in their column A:A which I need for my workbook, but the provider somehow input the item as "TYPH02." into their cell (note there's a dot immediately after the item), and this rendered my function blind & cannot match anything. Otherwise, all other items that are "clean" could be MATCHED & INDEXED. I would guess my solution is to have soem form of modification to the MATCH function to work even if the provider attached some random ASCII characters to it.
Here's my existing function: =IF(symbol<>"",INDEX(source.xls!$A$2:$A$5000,MATCH(symbol,source.xls!$W$2:$W$5000,0)),"")
Stewart
Can anyone guide me in how to tweak my MATCH function?
Issue is the MATCH function would overlook the source data when the source file provider (for some silly reason) randomly attaches some random ASCII characters to some of the cells contents, for eg. if a DOT or a COMMA is attached to the letters, the MATCH will return nothing or not found when the item is there in the source. The MATCH function just works perfectly fine when the source data comes clean with no ASCII characters attached, and I have no way to control how the source organises the data contents.
Say, one of my symbol is "TYPH02" which corresponds to looked for data item in source file is TYPH02 that INDEXES for 2020_06_22 in their column A:A which I need for my workbook, but the provider somehow input the item as "TYPH02." into their cell (note there's a dot immediately after the item), and this rendered my function blind & cannot match anything. Otherwise, all other items that are "clean" could be MATCHED & INDEXED. I would guess my solution is to have soem form of modification to the MATCH function to work even if the provider attached some random ASCII characters to it.
Here's my existing function: =IF(symbol<>"",INDEX(source.xls!$A$2:$A$5000,MATCH(symbol,source.xls!$W$2:$W$5000,0)),"")
Stewart