0

In Excel for Mac 2011, I am trying to create a table (in columns D-G) that references the data in A1:B5. I want E1 to search column A and report the data in column B corresponding to the first match; F1 to search column A and report the second match; G1 the third match, etc. VLOOKUP would solve the problem for E1, but to my knowledge can't handle anything past the first match.

Here is the formula I have found in other help sites:

=INDEX($B$1:$B$5,SMALL(IF($A$1:$A$5=$D$1,ROW($A$1:$A$5)-MIN(ROW($A$1:$A$5))+1,""),COLUMN(A1)))

This works perfectly for E1, but results in a #NUM! error in cell F1. I entered the following formula into another cell and got a value of 1, which led me to believe that my formula is only finding one match:

=MAX((IF($A$1:$A$5=$D$1,ROW($A$1:$A$5)-MIN(ROW($A$1:$A$5))+1,"")))

I can't figure out where the first formula is going wrong. If anyone has any ideas, or other ways to do this, that would be greatly appreciated!

pnuts
  • 58,317
  • 11
  • 87
  • 139
Snicker94
  • 1

1 Answers1

0

You need to modify like:

=INDEX($B$1:$B$5;SMALL(IF($A$1:$A$5=$D$1;ROW($A$1:$A$5)-MIN(ROW($A$1:$A$5))+1;"");2))

intead of COLUMN(A1) you need to put the number of kth element.
you have a NUM# if there isn't the kth position in the array...
In that case you need to use a:

=IFERROR(INDEX($B$1:$B$5;SMALL(IF($A$1:$A$5=$D$1;ROW($A$1:$A$5)-MIN(ROW($A$1:$A$5))+1;"");2));"")

Obviusly the formula shell be inserted with CTRL + SHIFT + ENTER.

user3514930
  • 1,721
  • 1
  • 9
  • 7
  • Thanks for your help! Unfortunately it still isn't working. I am getting the NUM# error for k=2 even if there are multiple matches in the data. Is there anything wrong in the formula that should cause this issue? – Snicker94 Jul 28 '14 at 11:55