vlookup returning first value

[johnny52]

I have a small worksheet for an example 3 columns,6 rowss

col a1=1 a2=1 a3=1 a4=2 a5=2 a6=2
col b1=1 b2=2 b3=3 b4=1 b5=2 b6=3
col c1=1 c2=1 c3=1 c4=1 c5=2 c6=2

my vlookup formula in column D =vlookup(c1,$a$1:$b$10,2,false),"")
returns all 1's in col d1 through d6.....it's returning first value it finds from col B

the values in col b are not always numbers.......was wondering if there was an index match that might work

Thanks Everybody

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D
1	1	1	1	1
2	1	2	2	1
3	1	3	1	2
4	2	1	1	3
5	2	2	2	2
6	2	3	2	3
Indata

Cell Formulas
Range	Formula
D1	=INDEX($B$1:$B$6,AGGREGATE(15,6,(ROW($A$1:$A$6)-ROW($A$1)+1)/($A$1:$A$6=C1),COUNTIF($C$1:$C1,C1)))

 

[johnny52]

Thank You,

when I copy this formula I 'm getting #NAME ? in cell D1

 

[Fluff]

What version of Xl are you using?

 

[johnny52]

2007

 

[Fluff]

Ok, Aggregate didn't come in until 2010, try

Excel 2013/2016
	A	B	C	D
1	1	1	1	1
2	1	2	2	1
3	1	3	1	2
4	2	1	1	3
5	2	2	2	2
6	2	3	2	3
Indata

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

 

[johnny52]

That did the trick THANKS SO MUCH.......didn't notice your first response did show the example for 2013 2016

 

[Fluff]

You're welcome & thanks for the feedback

 

[johnny52]

what happened to the original reply from this earlier post ? The Array formula is buried in there somewhere Thanks

 

[Fluff]

It's a problem with the HTML that was allowed on the previous board, but not here. It will be sorted out, but will take some time.

I suspect the formula was
=INDEX($B$1:$B$6,SMALL(IF($A$1:$A$6=C1),ROW($A$1:$A$6)-ROW($A$1)+1),COUNTIF($C$1:$C1,C1)))