Help requested with formula

richanor

Active Member
Joined
Apr 8, 2006
Messages
291
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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.
 
Upvote 0
Assuming in Lookup sheet


L
M
1
London​
SDL Supplies - LONDON​
2
Bristol​
Buddies BRISTOL​
3

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.
 
Upvote 0
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
 
Upvote 0
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.
 
Last edited:
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top