• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Error with index formula

S

sachar

Member
Dear All,

With reference to the attached sample file, I could not get it what is the wrong with an array formula in Column “H”(CATEGORY).

I would like to indexing the columns ('DATA FILE'!$I$2:$N$2 ) of the category from “DATA FILE sheet” on the basis of [Column “C” (S.No.)].

Please help me.
 

Attachments

  • Complimentary Summary_catogry in row_practice_vlookup.xlsx
    28.3 KB · Views: 10
check this gives the right results.In H3, normally entered:
=INDEX('DATA FILE'!$I$2:$N$2,1,MATCH(1,INDEX('DATA FILE'!$I$3:$N$70,MATCH($C3,'DATA FILE'!$C$3:$C$70,0),0),-1))
 
upload_2017-4-24_19-46-22.png
So it looks like you could omit that 1.

upload_2017-4-24_19-50-3.png

upload_2017-4-24_19-52-26.png

View attachment 41114

upload_2017-4-24_19-57-5.png

So by the looks of it I could have used a smaller number - just in case you have values less than 1. I've assumed that there's only one value per row in this section of the table. You can miss that 1 out too:
=INDEX('DATA FILE'!$I$2:$N$2,,MATCH(,INDEX('DATA FILE'!$I$3:$N$70,MATCH($C3,'DATA FILE'!$C$3:$C$70,0),0),-1))
 
Last edited:
You must log in or register to reply here.
Back
Top