lookup needed to give value where the lookup value is a string within a cell.

[Christopher Flack]

Hello there

I am having issues using the file attachment thing so I've uploaded th
file to uppit http://up.ht/OjSSXH

I am trying to extract information from Column B to state the valu
after the space so in the instance of C7 it should say "JHD/Southampto
Resourcing" The problem I am having is that I can't seem to figure ou
how to create an IF function that will resolve as true (probably due t
text and number issues)

I need a lookup to just take the first one or two left hand character
from the cell and return the information minus the task number. So w
can filter it with auto filter later.

I have 8 sheets with this information that I need to go through. This i
an important task I can't afford to do it manually as there is too muc
propensity for human error. Whilst I was creating this example sheet
made a mistake in the manual entry in column D, which is corrected now

The data is sourced from some other formulas, which are workin
perfectly.

Combined with that if you look in cell B10 you will see that there i
one instance of the explanation for task 3. I need it to sa
"JHD/Resourcing" on both occasions (cells D1112)

If anyone knows an array formula that can be entered into column C tha
can achieve what I have detailed columns D + E I would be grateful.

I tried =LEFT(B7,1) which resolves as 1 and the value in cell A7 is 1
But for some reason when I try =LEFT(B7,1)=A7 it is resolving as fals
which is preventing me from advancing what I want to do. (see attache
sheet)

Please let me know if you need further description

 

[Kevin@Radstock]

Hi Christopher Flack

=IF(LEFT(B7,1)+0=A7,RIGHT(B7,LEN(B7)-FIND(" ",B7)),"")

Cell A7 is a number and Cell B7 is text.

 

[Spencer101]

Hello there

I am having issues using the file attachment thing so I've uploaded th
file to uppit http://up.ht/OjSSXH

I am trying to extract information from Column B to state the valu
after the space so in the instance of C7 it should say "JHD/Southampto
Resourcing" The problem I am having is that I can't seem to figure ou
how to create an IF function that will resolve as true (probably due t
text and number issues)

I need a lookup to just take the first one or two left hand character
from the cell and return the information minus the task number. So w
can filter it with auto filter later.

I have 8 sheets with this information that I need to go through. This i
an important task I can't afford to do it manually as there is too muc
propensity for human error. Whilst I was creating this example sheet
made a mistake in the manual entry in column D, which is corrected now

The data is sourced from some other formulas, which are workin
perfectly.

Combined with that if you look in cell B10 you will see that there i
one instance of the explanation for task 3. I need it to sa
"JHD/Resourcing" on both occasions (cells D1112)

If anyone knows an array formula that can be entered into column C tha
can achieve what I have detailed columns D + E I would be grateful.

I tried =LEFT(B7,1) which resolves as 1 and the value in cell A7 is 1
But for some reason when I try =LEFT(B7,1)=A7 it is resolving as fals
which is preventing me from advancing what I want to do. (see attache
sheet)

Please let me know if you need further description.

Hi Christopher,

Have a look at the attached.
I've added a formula to column D that does what you want. You could us
a different formula that just drops the 2 characters to the left, bu
the one I've used will take into account double digit numbers (or more
to make life easy for you.

I've also shown why the =LEFT(B7,1)=A7) doesn't work... Well not so muc
WHY it doesn't work, but how to get it to work.

Hope that's of help.

S

+-------------------------------------------------------------------
|Filename: Christopher Flack Example.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=491
+-------------------------------------------------------------------