Formula using INDEX, SMALL, and ISERROR functions gives skewed results

**rspells** · 11-08-2013, 10:11 AM

Help, I am using a function that I found on an "office.microsoft.com" help site that should let me look up a value in a list and return multiple corresponding values from another column in the same spreadsheet. As you will see from my attached spreadsheet, when I enter a "Group" number the results are skewed, that is, they are shifted down by one row. This happens no matter what "Group" I choose. I did test on the SMALL function portion of the formula that finds the rows where the results are found and it is accurate. However, I believe the INDEX function is where things go wrong.

Please tell me what I'm doing wrong, or suggest a simpler Excel formula that will work correctly.

**benishiryo** · 11-08-2013, 10:30 AM

welcome to the forum, rspells. your INDEX array portion starts from B2. that is your 1st row. when you get a match here:
IF('Index help.xls'!Groupdata=Key
you then put in:
ROW('Index help.xls'!Groupdata)

so your 1st match is in B13. B13 is the 12th & not 13th row. so people usually do it like this:

Formula:

Please Login or Register  to view this content.

my preferred way & to shorten it would be:

Formula:

Please Login or Register  to view this content.

**rspells** · 11-08-2013, 01:28 PM

Thanks benishiryo. I used your prefered, shortened version of the formula and it works like a charm.

**arlu1201** · 11-09-2013, 03:32 AM

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.