Skip a previously used cell

[hannah2233]

Hi Guys, I am trying to use vlookup (or match/index) to return a value in an array but if the number is repeated, the formula I am using always returns the first match it finds. I've put a simplified version of what I mean below:

1 Cat
1 Dog
2 Fish

In the first cell I am using =vlookup(1,$A$1:$B$3,2,False) which return "Cat", excellent!
But in the second cell I want to return 'Dog" but if I use the same formula it returns "Cat" again.
I need a formula, using the same array, which would ignore cat and return dog


Cheers,
Hannah xx

 

[sheetspread]

Excel 2010
	A	B
1	1	A
2	1	B
3	1	E
4	2	R
5	2	Q
6	3	B
7	3	L
8	4	Z
9
10	1	A
11	1	B
12	1	E
13	2	R
14	2	Q
15	4	Z
Sheet22

Cell Formulas
Range	Formula
B10	{=INDEX($B$1:$B$8,SMALL(IF($A$1:$A$8=A10,ROW($A$1:$A$8)),COUNTIF($A$10:A10,A10)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[hannah2233]

Hi thanks for your response, however it doesnt quite work correctly...
I used the example you provided and it is still returning the letter A, where I believe it should return B or E.

By looking up the value of 1, I need to return the name 'Cat' and another cell to pick up 'Pig" in the following example

1 Dog
1 Cat
2 Fish
1 Pig
5 Emu
0 Donkey

Thank you so much
Hannah

 

[sheetspread]

It works on both examples when I try:

Excel 2010
	A	B
1	1	Dog
2	1	Cat
3	2	Fish
4	1	Pig
5	5	Emu
6	0	Donkey
7
8	1	Dog
9	1	Cat
10	2	Fish
11	1	Pig
12	5	Emu
13	0	Donkey
Sheet22 (2)

Cell Formulas
Range	Formula
B8	{=INDEX($B$1:$B$6,SMALL(IF($A$1:$A$6=A8,ROW($A$1:$A$6)),COUNTIF($A$8:A8,A8)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[hannah2233]

Thanks, it works when I use the columns A and B. but when I try insert a new row or shift the data it no longer functions correctly.
I have checked that the formula still samples the correct cells. This seems odd

 

[sheetspread]

Can you show me your data and the formula?

 

[sheetspread]

I have checked that the formula still samples the correct cells.

Be sure, the slightest difference will return incorrect results.

 

[hannah2233]

Sorry Just saw this....

I've just altered the spreadsheet so the values start in the first row, its non-ideal in terms of formatting and it will not be sensitive if I insert a row above row 1 but hey it works! In the working you provided try insert a row above and see if it still works, thats what the problem I was having.

Anyway I've found a work around, thank you so much

 

[sheetspread]

In the working you provided try insert a row above and see if it still works, thats what the problem I was having.

Excel 2010
	A	B
1
2	1	Dog
3	1	Cat
4	2	Fish
5	1	Pig
6	5	Emu
7	0	Donkey
8
9	1	Dog
10	1	Cat
11	2	Fish
12	1	Pig
13	5	Emu
14	0	Donkey
Sheet15

Cell Formulas
Range	Formula
B9	{=INDEX($B$2:$B$7,SMALL(IF($A$2:$A$7=A9,ROW($A$2:$A$7)-1),COUNTIF($A$9:A9,A9)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Yes, you have to adjust the row function because it's returning a 7 when you only have six rows in the range (hence the REF error). Unlike the cell references that are automatically shifted.