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
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies