Help requested with formula

[richanor]

Apologies for the generic subject - I didn't know how to sum this one up in a few words.

I have a workbook with 2 sheets - 'Sales' and 'Lookup'. The sales sheet is an output from our sales database - the columns of interest are column D (Account Name), Column I (Description), and Column AI (Account).

Column D contains the account name e.g. 'SDL Supplies', but this account may have several buying points e.g. Liverpool, London, Glasgow. In this case the buying point will be listed in column I. I would like to combine the account and buying point into one cell in column AI so it appears like 'SDL Supplies - LONDON'.

Normally I would use the concatenate function to do this, except sometimes there is other information in Column I - so it might appear like 'Order 145883, date: 12/3/19, Depot: LONDON', so I don't know if it's possible to just search for the word.

On the second sheet (Lookup) in column L, I have a list of all of the possible buying points that might appear in the description field and adjacent to it in column M, I have the Account name as I want it to appear in column AI, so for example:

Column L Column M
London SDL Supplies - LONDON
Bristol Buddies - BRISTOL

There is no overlap - each description in column L is unique. I was wondering whether there is some way to use a VLOOKUP but can't figure it out. I am sure the answer is staring me in the face - but if anybody can help I'd appreciate it.

Rich

 

[sparky2205]

Hi,
this is the first part, should get you started:
This will be your Column AI formula: =D2 & " " & MID(I2,FIND("Depot",I2)+7,LEN(I2)-FIND("Depot",I2)+8)
I'll have a look at the second part if I get time later.

 

[Marcelo Branco]

Assuming in Lookup sheet


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

L​

[/td][td="bgcolor: #DCE6F1"]

M​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

London​

[/td][td]

SDL Supplies - LONDON​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Bristol​

[/td][td]

Buddies BRISTOL​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td][/td][td][/td][/tr]
[/table]

Try in Sales AI2 copied down
=LOOKUP(2,1/(SEARCH(Lookup!L$1:L$100,I2)*(Lookup!L$1:L$100<>"")),Lookup!M$1:M$100)

Hope this helps

M.

 

[richanor]

Amazing, thanks Sparky - I'll try it now

Rich

 

[richanor]

Thankyou Marcelo - really appreciate the help!

Rich

 

[richanor]

Hi Marcelo

Thanks again for your solution. It seems to do *something*, but isn't quite working correctly. I cant establish exactly what the problem is because I don't fully understand the formula - but it puts 'SDL Supplies - LONDON' in many of the cells in column AI even when LONDON doesn't appear in COLUMN D. Also there are no other account names - SDL Supplies - LONDON is the only thing that appears in column AI.

Do you know why this may be?

Rich

 

[Marcelo Branco]

The formula checks if each word in Lookup! L$1:L$100 is present in the cell of column I (I2 in the first formula), not in column D

=LOOKUP(2,1/(SEARCH(Lookup!L$1:L$100,I2)*(Lookup!L$1:L$100<>"")),Lookup!M$1:M$100)

M.

 

[sparky2205]

Hi Rich,
I don't know if you got your issue sorted out. I was looking at Marcelo's formula.
It works perfectly except that he has switched columns L and M in his example. See post #3 . If I read your initial description correctly I think you wanted the L values in M and vice versa?
If that is the case just switch the L and M values in Marcelo's formula and you should be fine:
=LOOKUP(2,1/(SEARCH(Lookup!M$1:M$100,I2)*(Lookup!M$1:M$100<>"")),Lookup!L$1:L$100)

 

[Marcelo Branco]

Hi Rich,
I don't know if you got your issue sorted out. I was looking at Marcelo's formula.
It works perfectly except that he has switched columns L and M in his example. See post #3 . If I read your initial description correctly I think you wanted the L values in M and vice versa?

Maybe you're right, because I also was not sure about the L and M columns of the Lookup sheet.

But, when I saw this in the initial post
"On the second sheet (Lookup) in column L, I have a list of all of the possible buying points that might appear in the description field and adjacent to it in column M, I have the Account name as I want it to appear in column AI, so for example:"

I assumed as I showed above (see post 3).

M.

 

[sparky2205]

And you did make your assumptions clear in post 3.
Let's hope Rich gets back to us and let's us know if this works for him.
Looks like it should.