Try this:

{=INDEX(Vehicles!A$1:B$10,SMALL(IF(Vehicles!A$1:A$ 10='Depot
info'!$B$2,ROW(Vehicles!A$1:A$10),ROW(Vehicles!A$1 0)+1),ROW('Depot
info'!A1)),2)}

Note: Copy the formula excluding '{' '}' and paste in required cell then
press the following keys Ctrl+Shift+Enter (don't press only Enter key)

1. Insert a row above the list in Vehicles sheet
2. Insert the above formula in 'cell C2' in Depot info sheet
3. Fill down cell C2 to the no.of cells you want.

In the formula I've taken 10th row as the last row in Vehicles sheet you can
replace 10 with your required number which is last row in vehicles sheet.

Regards
~Vins (Vinod)

"Sunnyskies" wrote:

Hi from Sunny RSA,

I have a list on sheet Vehicles:
Column A Column B
BEM 98D03
ALR 97R03
BEM 96R02

Now on another sheet called Depot info, I have got:
BEM (in cell B2)
In cell C2 I want a formula that will return 98D03 and in cell C3 I want to
return 96R02

So the formula must vlookup return the value, then in the next cell below
run the vlookup again and if the result is the same as in cell c2, do not
return a value, but if it is different ..... haha that is the problem

Thanks