Lookup part of a cell 4

[walla07]

Hi,

Is it possible to lookup and match on part of the data within a single cell, without running a text to columns to split the cell.

For example, I have an order number A123456. A separate column shows the orders received by date and one cell could contain 'A123455, A123456, A123457, A123458'.

Is there a function that would pick up A123456 in the cell and return the date?


Thanks in advance for any help.

 

[xlbo]

not in that format - you can look up part of a cell into a set of data but you cannot lookup a cell into part of a cell

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[CautionMP]

walla07,
You will have to nestle some formulas, [tt]Match()[/tt] will find the internal value and return an array index, you can then use [tt]Index()[/tt] on to pull this value out of the array.

I did a quick test in Excel 2000 and the [tt]Match()[/tt] function returned the correct index for the cell with [tt]A123455, A123456, A123457, A123458[/tt] in it when I used any of the four values.

Hope this helps,
CMP



(GMT-07:00) Mountain Time (US & Canada)

 

[xlbo]

Be careful with this

CautionMP - To do this, you would need a 'non exact match' in the Index statement

If you do that, I fail to see how all 4 values will return a hit.

If you use 0 for the match type, none will be returned as found as it looks for an exact match

-1 will match all but the highest value (but also return hits for lower values)
1 will match all but the lowest value (but also return hits for higher values)

.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

Yes, it's possible. It's tricky without seeing your data setup. Check out these Fuzzy Match links...

http://www.mrexcel.com/pc07.shtml

http://www.dicks-blog.com/archives/2004/06/16/fuzzy-text-match/

http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21712818.html

HTH

-----------
Regards,
Zack Barresse

 

[xlbo]

no real need for a fuzzy match. If you go down the VBA route, you could happily use the FIND method to perform the search for you

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

Yeah, VBA is much more flexible in situations like this. You know what would be nice? To have the LIKE (VBA) function as a worksheet function and not have to create a UDF if we wanted to use it. It's possible with formulas (if the data structure is condusive) but more of a pain in the backside. (IMHO)

-----------
Regards,
Zack Barresse

 

[KenWright]

So assuming your search value was in say D2, and your dates were in say C4:C1000 and your order numbers were in say D41000, how about

=SUMPRODUCT(--ISNUMBER(SEARCH($D$2,D41000)),--C4:C1000)

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

That would of course only work if there was just a single match, duplicates would screw that up.

Assuming there was only one though, and if you wanted the row that it was to be found on, then you could also look at something like:-

=SUMPRODUCT(--ISNUMBER(SEARCH($D$2,D41000)),ROW(C4:C1000))

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

Accounting for duplicates and returning the first match in the list:-

Array entered using CTRL+SHIFT+ENTER

=SUMPRODUCT(LARGE((--ISNUMBER(SEARCH($D$2,D41000))*ROW(C4:C1000)),COUNTIF(D41000,"*"&D2&"*")))

For last match in list, then again array entered:-

=SUMPRODUCT(MAX(--ISNUMBER(SEARCH($D$2,D41000))*ROW(C4:C1000)))

Regards
Ken.........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

LOL - you just wanted to see if it was possible didn't ya Ken !!

firefytr - yeh - having a 'Like' function in excel formulae would make a lot of things easier !!

As Bill's 'chosen ones' can you n Ken not have a word in his shell like ? ;-)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[KenWright]

LOL - It was that swear word 'VBA' that got me all provoked into some of kind of action

(Also it was early and I just couldn't get my head around actually starting work - needed a diversion)

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[firefytr]

LOL! Chosen ones .. ha! More like lambs to the slaughter! LOL! I haven't checekd the new beta for a function like that. I'm so used to using my UDF I don't hardly think about it anymore.

(Hiya Ken!)

-----------
Regards,
Zack Barresse

 

[walla07]

Hi,

Thank you very much for everyone's time and effort in helping with this. I'm afraid I've not been able to try the solutions yet, but rest assured there are stars and thanks a-plenty as soon as I get chance.

Thanks again.

 

[KenWright]

Hi Zack - I haven't installed the latest beta yet!! Decided I needed a bit of a change in my life and it kinda revolves around lots of time at the gym (club) and spending lots more time with my family at the moment (and less at work and less on the computer at home), and funnily enough I like it!!!

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[walla07]

Ken,

Thank you very much for those formulas, they work brilliantly.

 

[KenWright]

You're very welcome

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------