Nomoreheroes
The Moral Majority
 Offline
Posts: 15025
|
 |
« on: Friday, June 14, 2013, 14:44:51 » |
|
I'm trying to get Excel to help me do some financial reconciliation, but have stumbled across a problem.
I'm using the following formula:
=VLOOKUP(B39,'NMH Data'!A$1:B$147,2)
The thing I am stuck on is that cell B39 only has part of the string that is in NMH Data. The formula only works if B39 is an exact match for a cell in NMH Data.
e.g At present, cell B39 has the following Data in it - No More Heroes
Somewhere in 'NMH Data' between A1-B147 I have a cell that has - No More Heroes 10031524297940000R
So how can I ensure that 'No More Heroes 10031524297940000R' is found when I ask the formula to search for 'No More Heroes' ?
|
|
|
|
|
Logged
|
You're my incurable malady. I miss the pleasure of your company.
|
|
|
|
TungstenCarbide
|
|
« Reply #1 on: Friday, June 14, 2013, 14:52:38 » |
|
fuck me that's boring
|
|
|
|
|
Logged
|
|
|
|
Simon Pieman
Original Wanker
Offline
Posts: 36320
|
|
« Reply #2 on: Friday, June 14, 2013, 14:59:21 » |
|
Change the B39 to:
"*"&B39&"*"
Tested and works in Excel 2010.
|
|
|
|
|
Logged
|
|
|
|
Nomoreheroes
The Moral Majority
Offline
Posts: 15025
|
|
« Reply #3 on: Friday, June 14, 2013, 15:08:40 » |
|
Change the B39 to:
"*"&B39&"*"
Tested and works in Excel 2010.
Doesn't work in Excel 2007 |
|
|
|
|
Logged
|
You're my incurable malady. I miss the pleasure of your company.
|
|
|
Simon Pieman
Original Wanker
Offline
Posts: 36320
|
|
« Reply #4 on: Friday, June 14, 2013, 15:11:07 » |
|
Doesn't work in Excel 2007
Upgrade to Excel 2010. Change the B39 to: "*"&B39&"*"  |
|
|
|
|
Logged
|
|
|
|
Nomoreheroes
The Moral Majority
Offline
Posts: 15025
|
|
« Reply #5 on: Friday, June 14, 2013, 15:13:25 » |
|
fuck me that's boring
Upgrade to Excel 2010. Change the B39 to: "*"&B39&"*" Sense of humour failure at this end! Are you serious that it worked in Excel 2010 or was the whole string a wind up? |
|
|
|
|
Logged
|
You're my incurable malady. I miss the pleasure of your company.
|
|
|
Nomoreheroes
The Moral Majority
Offline
Posts: 15025
|
|
« Reply #6 on: Friday, June 14, 2013, 15:14:17 » |
|
fuck me that's boring
Yes. Yes it is. And this is something I am choosing to do in my free time! |
|
|
|
|
Logged
|
You're my incurable malady. I miss the pleasure of your company.
|
|
|
Samdy Gray
Dirty sneaky traitor weasel
Offline
Posts: 27143
|
|
« Reply #7 on: Friday, June 14, 2013, 15:14:28 » |
|
You probably want to use some variation of MATCH instead of VLOOKUP, as MATCH accepts wildcards such as *.
|
|
|
|
|
Logged
|
|
|
|
Simon Pieman
Original Wanker
Offline
Posts: 36320
|
|
« Reply #8 on: Friday, June 14, 2013, 15:16:50 » |
|
Sense of humour failure at this end!
Are you serious that it worked in Excel 2010 or was the whole string a wind up?
It works. I tried it 5 minutes ago. |
|
|
|
|
Logged
|
|
|
|
Simon Pieman
Original Wanker
Offline
Posts: 36320
|
|
« Reply #9 on: Friday, June 14, 2013, 15:18:40 » |
|
Actually you need to modify the formula to this:
=VLOOKUP("*"&B39&"*",Sheet2!A$1:B$147,2,FALSE)
|
|
|
|
|
Logged
|
|
|
|
Bewster
Offline
Posts: 4004
We fucking love you Gumbo!
|
|
« Reply #10 on: Friday, June 14, 2013, 15:21:08 » |
|
Actually you need to modify the formula to this:
=VLOOKUP("*"&B39&"*",Sheet2!A$1:B$147,2,FALSE)
If you use "True" instead of "False" it will lookup anything that is similar and not an exact match. |
|
|
|
|
Logged
|
|
|
|
Nomoreheroes
The Moral Majority
Offline
Posts: 15025
|
|
« Reply #11 on: Friday, June 14, 2013, 15:22:13 » |
|
Actually you need to modify the formula to this:
=VLOOKUP("*"&B39&"*",Sheet2!A$1:B$147,2,FALSE)
Just tried it with this: B39&"*" And it worked! Thanks matey !!!!! |
|
|
|
« Last Edit: Friday, June 14, 2013, 15:25:25 by Nomoreheroes »
|
Logged
|
You're my incurable malady. I miss the pleasure of your company.
|
|
|
Simon Pieman
Original Wanker
Offline
Posts: 36320
|
|
« Reply #12 on: Friday, June 14, 2013, 15:22:47 » |
|
If you use "True" instead of "False" it will lookup anything that is similar and not an exact match.
I initially tried that and it didn't work |
|
|
|
|
Logged
|
|
|
|
Nomoreheroes
The Moral Majority
Offline
Posts: 15025
|
|
« Reply #13 on: Friday, June 14, 2013, 15:24:55 » |
|
Next question is:
I'm finding that when the string oin B39 is not present in 'NMH Data' it comes back and puts a random number into the cell. How do I stop it doing that?
|
|
|
|
|
Logged
|
You're my incurable malady. I miss the pleasure of your company.
|
|
|
|
TungstenCarbide
|
|
« Reply #14 on: Friday, June 14, 2013, 15:28:18 » |
|
Suggestions for stopping that happening;
A) Throw the machine out of a window
B) Throw yourself out of a window
|
|
|
|
|
Logged
|
|
|
|
|
