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

 

[richanor]

Hi Marcelo and Sparky

Thank you very much for your help - Marcelo had interpreted my post correctly (even though it wasn't very clear) but I had made the schoolboy error of pasting the formula into cell AI2284 without updating the row number in the formula (so it was searching the contents of I2). I realised as soon as Marcelo explained wha tthe formula was doing. All working great now.

you have both been very helpful and generous with your time - much appreciated!

Rich

 

[Marcelo Branco]

You're very welcome. Glad we've helped.

M.

 

[sparky2205]

It's always nice to find a solution.
I've been helped in this forum so many times I try to help others if I can.
At first I was reluctant as I'm nowhere near the level of guys like Marcelo.
Then I realised there are folk out there looking for help just like I was at the beginning.
Even someone quite experienced at Excel can learn something from a relative newbie.
It's such a vast application with so many possibilities. Nobody knows everything.
I regularly take a look at zero reply posts to see if there's anything I can help with.