Vlookup and return if cell contains

[cssfonseca]

Hi,

I'm trying to cross data to fill a sheet but I'm having some doubts.

I have a number a value in a sheet, and I want to complete it with data from another sheet.
For example:

Sheet 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]EAN[/TD]
[TD]Name[/TD]
[TD]Age[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

sheet 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]EAN[/TD]
[TD]Name[/TD]
[TD]Spec 1[/TD]
[TD]Spec 2[/TD]
[TD]Spec3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Age:1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Age:2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Age:5[/TD]
[/TR]
</tbody>[/TABLE]

I want to complete Column C in sheet 1, with the age that appears in sheet 2 (no problem if it copy's the whole cell), but it's not in a specific column. I tried to use vlookup & match, and it's returning an error. a Vlookup the EAN column, and use match the specific string "Age:".

Can u help me? Am i using the right formula?

 

[steve the fish]

Does this work?

=0+SUBSTITUTE(LOOKUP("zzz",INDEX(Sheet2!$C$2:$E$4,MATCH(A2,Sheet2!$A$2:$A$4,0),0)),"Age:","")

 

[cssfonseca]

Does this work?

=0+SUBSTITUTE(LOOKUP("zzz",INDEX(Sheet2!$C$2:$E$4,MATCH(A2,Sheet2!$A$2:$A$4,0),0)),"Age:","")

Doesnt work, probably it's my fault.
Is the zzz the EAN in the sheet1?

 

[steve the fish]

No it isnt. Needs to be the "zzz". Check the cell references are referencing the correct cells.