Prev: Converting Excel to HTML
Next: Automatically filling a cell from a worksheet based on a selection
From: Mark Dullingham on 14 May 2010 07:59
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
From: Jacob Skaria on 14 May 2010 08:20
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
 | 
Pages: 1
Prev: Converting Excel to HTML
Next: Automatically filling a cell from a worksheet based on a selection