x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 110

# Excel -- Index Match array giving incorrect result

Greetings,

I have a formula that is intended to retrieve a list of IP addresses from a sheet named ip. The ip sheet stores the ip addresses in the second column (B). Whenever I enter the name of the server into A2 on the lookup sheet it gives me the IP address that falls one cell below the intended result.

``````=IF(ISERROR(INDEX(ip!\$A\$2:\$B\$241,SMALL(IF(ip!\$A\$1:\$A\$241=\$A\$2,ROW(ip!\$A\$2:\$A\$242)),ROW(1:1)),2)),"",INDEX(ip!\$A\$2:\$B\$236,SMALL(IF(ip!\$A\$2:\$A\$242=\$A\$2,ROW(ip!\$A\$2:\$A\$242)),ROW(1:1)),2))
``````

If you have any questions let me know.

NeptuneIT
0
Neptune IT
1 Solution

Commented:
Diffcult to say, without seeing the worksheet.

but in the meanwhile, can you try

=IF(ISERROR(INDEX(ip!\$A\$2:\$B\$241,SMALL(IF(ip!\$A\$1:\$A\$241=\$A\$2,ROWS(ip!\$A\$2:\$A\$242)),ROW(1:1)),2)),"",INDEX(ip!\$A\$2:\$B\$236,SMALL(IF(ip!\$A\$2:\$A\$242=\$A\$2,ROWS(ip!\$A\$2:\$A\$242)),ROW(1:1)),2))
0

Finance AnalystCommented:
I believe you need to adjust all of the ranges referred to from the ip sheet to starting at the same row, ie ip!\$A\$2, you have some starting at ip!\$A\$1.

I don't think the finishing rows being different is an issue in this case but worth changing those to the same as well.

Thanks
Rob H
0

Finance AnalystCommented:
As an aside, you can probably shrink the formula using IFERROR(..) rather than IF(ISERROR(..)).

Syntax:

=IFERROR(Formula,ErrorResult)

In your case, (I have adjusted for all same ranges as mentioned above, but check the brackets!!):

=IFERROR(INDEX(ip!\$A\$2:\$B\$242,SMALL(IF(ip!\$A\$2:\$A\$242=\$A\$2,ROWS(ip!\$A\$2:\$A\$242)),ROW(1:1)),2)),"")

Thanks
Rob H
0

Author Commented:
ip!\$A\$1 is the header, is it still required?
0

Author Commented:
Also Rob, changing the \$A\$2 to \$A\$1 generates an empty result
0

Finance AnalystCommented:
I was suggesting the other way, make all ranges start at A2.
0

Commented:
>=IFERROR(INDEX(ip!\$A\$2:\$B\$242,SMALL(IF(ip!\$A\$2:\$A\$242=\$A\$2,ROWS(ip!\$A\$2:\$A\$242)),ROW(1:1)),2)),"")

You can't have ROWS in that part, Rob

Try this version, assuming you enter the formula in D2 and copy down

=IFERROR(INDEX(ip!\$A\$2:\$B\$242,SMALL(IF(ip!\$A\$2:\$A\$242=\$A\$2,ROW(ip!\$A\$2:\$A\$242)-ROW(ip!\$A\$2)+1),ROWS(D\$2:D2)),2),"")

confirmed with CTRL+SHIFT+ENTER

regards, barry
0

Older than dirtCommented:
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.