Find a number in a column then return a value from another cell

I have the follow data laid outs as follows;

E F G H
1 No. Descr Ref Layout
2 1 Name1 1,2 (Name1 Here)
3 2 Name2 3 (Name1 Here)
4 3 Name3 4 (Name2 Here)
(Name3 Here)

What I'm trying to achieve is, find which row in col G the number '1' is
found then place the value of col F in that row in Col H, then do the same
for '2' and so on.

Firstly is it posible to find the cell that contains a specifice number if
they are serperated by a , or any other punctuation.

Then using that information carry out a lookup.

I think this might involve array formula but I'm pretty clueless in this area.

Thanks in advance

Mark

Hi Mark

Try the below array formula..press CTRL+SHIFT+ENTER to enter the formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=formula}"

Apply the below formula in H2 and copy down as required.

=INDEX($F$2:$F$10,MATCH("*," & ROW(A1) & ",*", "," & G$2:$G$10 & ",",0))

--
Jacob (MVP - Excel)


"Mark Dullingham" wrote:

I have the follow data laid outs as follows;

E F G H
1 No. Descr Ref Layout
2 1 Name1 1,2 (Name1 Here)
3 2 Name2 3 (Name1 Here)
4 3 Name3 4 (Name2 Here)
(Name3 Here)

What I'm trying to achieve is, find which row in col G the number '1' is
found then place the value of col F in that row in Col H, then do the same
for '2' and so on.

Firstly is it posible to find the cell that contains a specifice number if
they are serperated by a , or any other punctuation.

Then using that information carry out a lookup.

I think this might involve array formula but I'm pretty clueless in this area.

Thanks in advance

Mark

Thank you Jacob yoyr solution is exactly what I was looking for

Many thanks

Mark

"Jacob Skaria" wrote:

Hi Mark

Try the below array formula..press CTRL+SHIFT+ENTER to enter the formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=formula}"

Apply the below formula in H2 and copy down as required.

=INDEX($F$2:$F$10,MATCH("*," & ROW(A1) & ",*", "," & G$2:$G$10 & ",",0))

--
Jacob (MVP - Excel)


"Mark Dullingham" wrote:

I have the follow data laid outs as follows;

E F G H
1 No. Descr Ref Layout
2 1 Name1 1,2 (Name1 Here)
3 2 Name2 3 (Name1 Here)
4 3 Name3 4 (Name2 Here)
(Name3 Here)

What I'm trying to achieve is, find which row in col G the number '1' is
found then place the value of col F in that row in Col H, then do the same
for '2' and so on.

Firstly is it posible to find the cell that contains a specifice number if
they are serperated by a , or any other punctuation.

Then using that information carry out a lookup.

I think this might involve array formula but I'm pretty clueless in this area.

Thanks in advance

Mark