VLookup with text string

[BenCxys]

Hi,

I am not that strong in excel but I would like to seek some help from experts here.

I have 1 file with 2 sheet attached. In sheet 1 (E5), I have a part V07040.01.579 but in Sheet 2 ,I have a part 040.01.579. They are the same item. Using normal VLookup is unable to extract it respectively data I want.

Is there any advise for this?

=VLOOKUP(D51,MMS_29Sept2017!B:C,2,FALSE)

D51 = V07040.01.579
MMS_29Sept2017!B:C = sheet 2 column B and C , if B match D51, it display a reading = created but as Column B = 040.01.579, it unable to retrieve any reading. I have a column full of not complete text string so if is possible to churn out using any method will be great!

Rgd,
Ben

[Richard Buttrey]

The files weren't attached, (the paperclip icon doesn't work, you need to look underneath a post for the Manage Attachments option and go from there,

But assuming the strings are consistent and you can always drop the first 3 characters to get to the part number then

Please Login or Register  to view this content.

[BenCxys]

Hi, have attached. My apologies, some have more than 3 character different or different text format but the keyword will be the same.

[Richard Buttrey]

Hi, have attached. My apologies, some have more than 3 character different or different text format but the keyword will be the same.

Please clarify what you mean by 'the keyword will be the same'.

Excel needs a rule to be able to slice the string.
It's also complicated in that TSK*171906100 contains a space character within the string on sheet 2.

We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see.

[BenCxys]

Hi Richard,

I try to upload my real file.

I am trying to extract a data if it match the value I am trying to search.

If the value is a text with space in between or if the text is with only 8 digit but my value is a 12 digit value, vlookup return NA unless the text is also 12digit.

I have tried wildcard vlookup but unable to extract the data, index match with wildcard also unable.

If value I searching for match, it will provide me with a data.

Value search - V07040.01.579
Data extract if match - 44-BOG1-BOG1-1234

But as my lookup array is 040.01.579, data is unable to extract.

I have cases that the lookup array is less that what the lookup value is hence NA return, cases where there is spacing between wildcards, cases where there is alphaet with number lookup value hence NA return.

The best case is if my lookup value is the same as the lookup array, the data will be extracted.

Any solutions?

[Phuocam]

Try this ...

=LOOKUP(2,1/COUNTIF(D3,"*"&MMS_29Sept2017!$B$2:$B$6),MMS_29Sept2017!$C$2:$C$6)

Or:

=LOOKUP(2,1/SEARCH(MMS_29Sept2017!$B$2:$B$6,D3),MMS_29Sept2017!$C$2:$C$6)

[FlameRetired]

Also in the example that isn't a space character. It is a character 160.

If this is representative of what you have a thorough data cleaning is in order.

There may be a simpler formula. This monster is what I used in F4 and filled down. The bulk of the formula is a repeating cleanup of B2:B6. It must be array entered.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

[protonLeah]

If based on last nine chrs, maybe:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[BenCxys]

Hi ,

I have uploaded a copy of the workfile I working at.

I am trying to extract a data with the value if match but i encounter maybe some extra word with letter or some with lesser letter in it. Please kindly advise

Thanks

[BenCxys]

Hi all,

Any advise? Tks