formula error Index and Match function

%3CLINGO-SUB%20id%3D%22lingo-sub-3081008%22%20slang%3D%22en-US%22%3ERe%3A%20formula%20error%20Index%20and%20Match%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3081008%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1290140%22%20target%3D%22_blank%22%3E%40kivaavik%3C%2FA%3E%26nbsp%3BNot%20sure%20what%20you%20are%20trying%20to%20achieve%20but%20I%20find%20it%20a%20bit%20odd%20that%20the%20%22match%20ranges%22%20are%20part%20of%20the%20array%20you%20index.%20Normally%20you%20would%20refer%20to%20row%20and%20column%20headers.%20Perhaps%20move%20column%20B%20to%20column%20A%20and%20then%20change%20the%20indexed%20range%20to%20%24B%243%3A%24F%2417.%20But%20perhaps%20I'm%20all%20wrong.%20can%20you%20upload%20a%20screenshot%20of%20the%20relevant%20part%20of%20the%20file%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3080858%22%20slang%3D%22en-US%22%3Eformula%20error%20Index%20and%20Match%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3080858%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20created%20a%20dropdown%20of%20names%20and%20wanted%20to%20see%20the%20quality%20scores%20of%20each%20one%20in%20four%20different%20quality%20categories.%26nbsp%3B%20So%2C%20each%20time%20I%20change%20the%20name%20using%20the%20dropdown%20I%20should%20be%20able%20to%20fetch%20the%20corresponding%204%20rows.%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDEX(%24A%242%3A%24F%2417%2CMATCH(%24I4%2C%24B%242%3A%24B%2417%2C0)%2CMATCH(H%244%2C%24A%242%3A%24F%242%2C0))%3C%2FP%3E%3CP%3EHowever%2C%20I%20am%20being%20able%20to%20get%20only%20the%20first%20category%2C%20and%20the%20rest%20of%20them%20are%20%23N%2FA%20errors.%26nbsp%3B%20For%20all%20the%20names%20I%20am%20only%20being%20able%20to%20fetch%20the%20value%20of%20only%20one%20category%20from%20the%20mother%20table.%20Since%20morning%20tried%20several%20times%20to%20no%20avail.%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help%20me!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%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-3080858%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-3082704%22%20slang%3D%22en-US%22%3ERe%3A%20formula%20error%20Index%20and%20Match%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3082704%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1290140%22%20target%3D%22_blank%22%3E%40kivaavik%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFormula%20itself%20works%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20658px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F343435i90D2AD6CF413E9B9%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EAs%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3Bmentioned%2C%20better%20with%20sample.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3090491%22%20slang%3D%22en-US%22%3ERe%3A%20formula%20error%20Index%20and%20Match%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3090491%22%20slang%3D%22en-US%22%3EThank%20you%20very%20much%20Riny_van_Eekelen%20for%20taking%20time%20out%20to%20answer%20my%20query.%20I%20can%20do%20this%20now!%20The%20cell%20reference%20was%20getting%20changed%20for%20the%20following%20rows%20resulting%20in%20me%20being%20able%20to%20extract%20only%20the%20first%20row.%20I%20put%20a%20dollar%20sign%20to%20fix%20the%20row%20and%20it%20got%20cured%20of%20its%20error.%20Thanks%20again!!%3C%2FLINGO-BODY%3E
New Contributor

I have created a dropdown of names and wanted to see the quality scores of each one in four different quality categories.  So, each time I change the name using the dropdown I should be able to fetch the corresponding 4 rows. 

=INDEX($A$2:$F$17,MATCH($I4,$B$2:$B$17,0),MATCH(H$4,$A$2:$F$2,0))

However, I am being able to get only the first category, and the rest of them are #N/A errors.  For all the names I am only being able to fetch the value of only one category from the mother table. Since morning tried several times to no avail. 

Please help me! 

 

 

 

 

6 Replies

@kivaavik Not sure what you are trying to achieve but I find it a bit odd that the "match ranges" are part of the array you index. Normally you would refer to row and column headers. Perhaps move column B to column A and then change the indexed range to $B$3:$F$17. But perhaps I'm all wrong. can you upload a screenshot of the relevant part of the file?

@kivaavik 

Formula itself works

image.png

As @Riny_van_Eekelen mentioned, better with sample.

Thank you very much Riny_van_Eekelen for taking time out to answer my query. I can do this now! The cell reference was getting changed for the following rows resulting in me being able to extract only the first row. I put a dollar sign to fix the row and it got cured of its error. Thanks again!!
Thank you Sergei Baklan for your answer. The issue got resolved and I feel a little ashamed because it was a simple referencing issue. I am just learning Excel so shall keep going back and forth until I gain mastery. Thank you very much for accommodating.

@Sergei Baklan Thanks. 

@kivaavik , you are welcome