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

Pivot table will solve your problem


--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Sunnyskies" escreveu:

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

One way of doing it is to put this formula in cell C1 of your Vehicles
sheet:

=IF(A1="","",A1&"_"&COUNTIF(A$1:A1,A1))

and copy this down - it will give you a unique sequential code for
each item in column A.

Then in C2 of your Depot sheet, you can use this formula:

=IF(ISNA(MATCH(B$2&"_"&ROW(A1),Vehicles!C:C,0)),"" ,INDEX(Vehicles!
B:B,MATCH(B$2&"_"&ROW(A1),Vehicles!C:C,0)))

and copy this down for as many rows as you need.

Hope this helps.

Pete

On Jul 4, 2:19*pm, 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

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