# 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.

Thanks in advance

Mark

Ad

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

Ad

M

#### Mark Dullingham

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

Many thanks

Mark

## Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.