Make sure that you confirm the formula with CONTROL+SHIFT+ENTER, not
just ENTER. In other words, after typing the formula, instead of
hitting ENTER, hold both the CONTROL and SHIFT keys down, then while
those two keys are held down, hit ENTER. Excel will place braces {}
around the formula indicating that you've entered the formula correctly.

Does this help?

In article ,
j2thea wrote:

That does help, however it is outputting the same number in 2 rows, instead
of giving me the first match and the second match.

"Domenic" wrote:

Try the following array formula that needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

E1, copied down:

=IF(ROWS(E$1:E1)<=COUNTIF($A$1:$A$4,$D$1),INDEX(B$ 1:B$4,SMALL(IF($A$1:$A$
4=$D$1,ROW($B$1:$B$4)-ROW($B$1)+1),ROWS(E$1:E1))),"")

....where D1 contains your criterion, such as 1110.

Hope this helps!

In article ,
j2thea wrote:

My table for vlookup: A1:B4
1110 2 or it can have one key 1110 2
1110 3 3
1120 4 1120 4
1120 5 5

I need the function to return all data associated with the value.
so when I look up 1110, i get 2 and 3 in different cells such as
2
3