Hi,
So I have a sheet "OUT" and in B1 of this sheet I have data of purchases i.e. "'plusnet 876249"
In C1 I want to take the data from B1, and search for a match in column A on another sheet "DATA", and return a corresponding value from the cell next to the match.
The thing is, I just want to search for the term Plusnet, as the numbers on the end will always be changing.
Can somebody please help? I've tried wildcards but not sure I was doing it properly.
Cheers
Last edited by adikerr; 11-04-2014 at 07:43 AM.
Hi AdiKerr
Don't think there's an easy way to get around this, as you are trying to match up "things that don't match" ! What happens when there are multiple "plusnet" matches in your sheet 'DATA'? Or are there no duplications of that name?
Pete
Pete
Struggling Solutions Architect
Hi,
a first approach could be
Formula:
Please Login or Register to view this content.
A sample file could be helpful to adjust formula references
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
Hi,
---------
sorry: I've probably misread OP answer
-------------------
For goodness sake, is this a help forum, or a politically correct title form? Sometimes the people who ask questions don't really know how to explain what their problem is, and that's why they are in this position with the "inappropriate" titles???
Perhaps, the moderators might be better qualified offering a more suitable "title" than putting someone, sometimes under pressure to fix a problem at work, under the pressure of the new problem of re-adjusting the title for which they don't really know what to ask for in the first place?
It's an Excel Help Forum with some rules. When ALL of us registered here we agreed to comply with them. So let's do it!Originally Posted by dubcap01
For goodness sake, is this a help forum, or a politically correct title form?
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
It's an Excel Help Forum with some rules...
agreed Fotis, but some people just don't know HOW to explain their problem properly. Perhaps suggesting a more suitable title would be more appropriate? Does four or more infraction notices in less than hour means over-zealous moderators, when all that is needed is guidance, or is this a systemic problem over all the forums and four in an hour is a massive underestimate?
And does anyone have the formula for working out the statistics of these infractions - ?
cheers
Pete
Hi Pete,
Thanks for your reply.
There will only be one instance of plusnet in my data sheet.
Maybe I can simplify my question? Is there a way to take B1 or "plusnet 876347" and look for a partial match in A1:A100 on my Data Sheet? If it finds a partial match i.e. just "plusnet" in (for example) A36, then it will return the value of B36.
Also, changed my post title to something a little more vague.....
Do you mean something like this?
adikerr - more vague? - mwaahaha
This yorkshireman appreciates the humour
Hope you get your answer - am working on it
Hi,
in the attached file I'm trying an array sum
Formula:Please Login or Register to view this content.
to be confirmed with control+shift+enter.
I'd need a sample file with some examples of expected results to adjust formula.
Regards
Hi,
in C4 same approach
Formula:Please Login or Register to view this content.
Regards
Thanks for your efforts and help so far guys.
I've attached an Example sheet which should make me a little clearer.
On the "Out" sheet, in D2 I want to create a formula that;
1) Takes the text in C2, and looks for a partial match in Data Sheet, Column B. If it finds a match, then it will display the corresponding text from the next door cell in Column C.
For example, if my imaginary formula were in D2, it would take the text from C2, "'8470 03JUN14 , VODAFONE LTD , LONDON GB", find a partial match in Data Sheet B3 "Vodafone", and would in turn display the value in C3, "Business Mobile Bill".
As you can see from the "Out" sheet, I can regularly have transactions which although are for the same thing, they have a different date on the end.
I don't want to strip the date on the end, I want to leave the original text intact for later cross reference to other docs.
Again,
Thanks for your help!
Example.xlsx
Hi,
in D2 and below you could try:
Formula:Please Login or Register to view this content.
Regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks