Vlookup Problem

[vivekshanghvi]

is it possible to look up multiple values from a table & get it one below the other.(reference is col A)
eg
Sheet 1
Col A Col B Col C
1 XYZ 4
1 ABC 5
1 PQR 6

i want Col C in sheet 2 using "1" as reference to look up & want to arrange 4, 5 & 6 in cell A1,A2 & A3
if any1 can help me..

 

[NBVC]

Try:

=IF(ROWS($A$1:A1)>COUNTIF(Sheet1!$A$1:$A$20,1),"",INDEX(Sheet1!$C$1:$C$20,SMALL(IF(Sheet1!$A$1:$A$20=1,ROW(Sheet1!$A$1:$A$20)),ROWS($A$1:A1))))

adjust ranges to suit.

confirm with CTRL+SHIFT+ENTER not just ENTER...you should see {} brackets around the formula.

 

[Aladin Akyurek]

Sheet1

Book1
	A	B	C	D
1	Field-1	Field-2	Field-3
2	1	XYZ	4
3	1	ABC	5
4	1	PQR	6
5
6
7
Sheet1

Sheet2

Book1
	A	B	C	D
1	1
2	3
3	Result(s)
4	4
5	5
6	6
7
Sheet2

A1: 1

which is the user-specified lookup value.

A2:

=COUNTIF(Sheet1!$A$2:$A$4,A1)

which determines the occurrence frequency of the lookup value.

A4:

=IF(ROWS($A$4:A4)<=$A$2,INDEX(Sheet1!$C$2:$C$4,SMALL(IF(Sheet1!$A$2:$A$4=$A$1,ROW(Sheet1!$A$2:$A$4)-ROW(Sheet1!$A$2)+1),ROWS($A$4:A4))),"")

which is confirmed with control+shift+enter (not with enter) then copied down.

Is this not the same thing as the one already posted? No, because:

1] It does not re-calculate the same thing many times.

2] It is robust against row insertions before the data on Sheet1. That is, it will always return the appropriate list.