Forum Discussion
formula error Index and Match function
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
SergeiBaklan Thanks.
- 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.
kivaavik , you are welcome
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?
- 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!!
