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

A	B	C
EAN	Name	Age
1
2
3

<tbody>
</tbody>

sheet 2

EAN	Name	Spec 1	Spec 2	Spec3
1		Age:1
2			Age:2
3				Age:5

<tbody>
</tbody>

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.