SOLVED
Home

Excel - Index Match Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-908286%22%20slang%3D%22en-US%22%3EExcel%20-%20Index%20Match%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-908286%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Guys%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20help%20related%20to%20Index%20and%20Match%20formula%2C%20I%20am%20getting%20%23N%2FA%20value%20from%20match%20getting%20the%20row%20number%2C%20I'm%20a%20beginner%20in%20writing%20this%20kind%20of%20formula%20so%20appreciate%20any%20help%20from%20the%20community.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20below%20formula%20I%20tried%20to%20run%3CFONT%20color%3D%22%23FF9900%22%3E%2C%20Orange%20color%20is%3C%2FFONT%3E%26nbsp%3Bmy%20array%20table%20range%20for%20the%20index%2C%20%3CFONT%20color%3D%22%23FF0000%22%3ERed%20color%20is%3C%2FFONT%3E%26nbsp%3Bmy%20row_num%20formula%20wherein%20I'm%20trying%20to%20get%20the%20left%20character%20of%20A2%20value%20which%20contains%202%20characters%2C%20it%20will%20be%20either%20a%20letter%20or%20a%20number.%20%3CFONT%20color%3D%22%230000FF%22%3EBlue%20color%20is%3C%2FFONT%3E%26nbsp%3Bmy%20column_num%20formula%20wherein%20I'm%20trying%20to%20get%20the%20right%20character%20of%20A2%20value%20which%20also%20contains%20either%20a%20number%20or%20a%20letter.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDEX(%3CFONT%20color%3D%22%23FF9900%22%3E'S-Box'!B2%3AQ17%3C%2FFONT%3E%2C%3CFONT%20color%3D%22%23FF0000%22%3EMATCH(IF(ISNUMBER(A2)%2CINT(LEFT(A2))%2CTEXT(LEFT(A2)%2C0))%2C'S-Box'!A2%3AA17%2C0)%3C%2FFONT%3E%2C%3CFONT%20color%3D%22%230000FF%22%3EMATCH(IF(ISNUMBER(A2)%2CINT(VALUE(RIGHT(A2)))%2CTEXT(RIGHT(A2)%2C0))%2C'S-Box'!B1%3AQ1%2C0)%3C%2FFONT%3E)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20on%20the%20red%20formula%20I'm%20getting%20the%20%23N%2FA%20error%2C%20I%20tried%20different%20method%20but%20seems%20I%20can%20only%20make%20it%20work%20when%20I%20tried%20to%20split%20the%20formula%20and%20not%20run%20it%20together.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20appreciate%20any%20help%20from%20you%20guys%20and%20I%20attached%20my%20sample%20file.%20Thanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-908286%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Index%20Match%20Formula%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-908550%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20Index%20Match%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-908550%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F424323%22%20target%3D%22_blank%22%3E%40elsu186%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20suggest%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDEX('S-Box'!%24B%242%3A%24Q%2417%2CHEX2DEC(LEFT(A2))%2B1%2CHEX2DEC(RIGHT(A2))%2B1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-909424%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20Index%20Match%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-909424%22%20slang%3D%22en-US%22%3EMany%20thanks%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%20it%20works...%20Appreciate%20your%20help...%3C%2FLINGO-BODY%3E
elsu186
New Contributor

Hi Guys,

 

I need help related to Index and Match formula, I am getting #N/A value from match getting the row number, I'm a beginner in writing this kind of formula so appreciate any help from the community.

 

Please see below formula I tried to run, Orange color is my array table range for the index, Red color is my row_num formula wherein I'm trying to get the left character of A2 value which contains 2 characters, it will be either a letter or a number. Blue color is my column_num formula wherein I'm trying to get the right character of A2 value which also contains either a number or a letter.

 

 

=INDEX('S-Box'!B2:Q17,MATCH(IF(ISNUMBER(A2),INT(LEFT(A2)),TEXT(LEFT(A2),0)),'S-Box'!A2:A17,0),MATCH(IF(ISNUMBER(A2),INT(VALUE(RIGHT(A2))),TEXT(RIGHT(A2),0)),'S-Box'!B1:Q1,0))

 

It is on the red formula I'm getting the #N/A error, I tried different method but seems I can only make it work when I tried to split the formula and not run it together.

 

I appreciate any help from you guys and I attached my sample file. Thanks

 

 

 

3 Replies
Solution

@elsu186 

I'd suggest

=INDEX('S-Box'!$B$2:$Q$17,HEX2DEC(LEFT(A2))+1,HEX2DEC(RIGHT(A2))+1)

 

Many thanks @Sergei Baklan it works... Appreciate your help...

@elsu186 , you are welcome, glad to help

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies