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

M

Mark Dullingham

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.

Mark

J

Jacob Skaria

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))

M

Mark Dullingham

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

Many thanks

Mark