matching an extracted character to one in a table in excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2591736%22%20slang%3D%22en-US%22%3Ematching%20an%20extracted%20character%20to%20one%20in%20a%20table%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2591736%22%20slang%3D%22en-US%22%3E%3CP%3EFor%20a%20letter%20extracted%20from%20a%20string%2C%20I%20am%20trying%20to%20find%20out%20in%20which%20row%20of%20the%20column%20is%20that%20character%20located.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20process%20to%20find%20an%20entered%20letter%20in%20E3%20works%20fine%2C%20but%20the%20match%20function%20will%20not%20take%20the%20character%20in%20E3%20when%20is%20a%20result%20of%20another%20function.%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20any%20way%20to%20do%20what%20I%20need%20to%20be%20done%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2591736%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2591861%22%20slang%3D%22en-US%22%3ERe%3A%20matching%20an%20extracted%20character%20to%20one%20in%20a%20table%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2591861%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1113769%22%20target%3D%22_blank%22%3E%40rcrichfl%3C%2FA%3E%26nbsp%3BPerhaps%20like%20so%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-07-27%20at%2017.46.45.png%22%20style%3D%22width%3A%20497px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F298710i68BD9A6A961B5A95%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-07-27%20at%2017.46.45.png%22%20alt%3D%22Screenshot%202021-07-27%20at%2017.46.45.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

For a letter extracted from a string, I am trying to find out in which row of the column is that character located. 

The process to find an entered letter in E3 works fine, but the match function will not take the character in E3 when is a result of another function. 

Is it any way to do what I need to be done?

7 Replies

@Riny_van_Eekelen 

 

Almost. I apologize I missed adding one parameter. The match must be able to distinguish upper case from lower case. Here is the new table. 

I tried your Mach suggestion and works as expected, but I cannot make it to discriminate by case type.

@rcrichflIOk, the you need to revert back to your original formula that uses EXACT, but change it a bit. The EXACT function takes the search value first, then the search array. You tried it the other way around. And easier if you use a named range for the entire list of characters (starting from A1), including the header. I called it myList in the attached workbook. Now it will find 8 as the row number. If you leave out the header it will find 7, being the 7th element in the array, starting in A2, And you will need to add 1 to return the row number.

 

 

@Riny_van_Eekelen 

Thank you for your effort. However, the formula you used works if the letter to look for is entered as a text in the cell, which is not what I am looking for. If you go back to my file, you will find that in that cell there is a function that extracts the character from a string in another cell. 

Therefore my original request is still open.

Kindly,

@rcrichfl That doesn't matter. Seems to work on my end.

Screenshot 2021-07-31 at 06.44.31.png

@Riny_van_Eekelen 

Thank you, Riny for your help. However, in the file you sent, cell E3 does not contain the LEFT(E2,1) command. Can you verify, please? I am using the same MATCH expression you show in E5. The only difference is that for the table I am using the range instead of a name. Anyways, I assigned a name to the table, and still, I get #N/A.

My apologies. Disregard my previous response. I missed to the the CTL+SHIFT+ENTER trick.