Part matching

[crl68]

Hi,

I have 2 sheets.

Sheet 1 has columns A-H where column B has about 300 references, e.g. ABC123XXY, DEF234.

Sheet 2 has columns A-D where column C has about 120 references which are shortened versions of these references, e.g. 123 instead of ABC123XXY or 234 instead of DEF234.

Sheet 1 also contains references that have no bearing on sheet 2.

Sheet 2 has column A containing a specific code for each reference.

I need to get the Sheet 2/Column A code to show against the relevant Sheet 1/column B reference on Sheet 1 however Sheet 2 only has shortened versions of Sheet 1 references so it's a partial match thing I can't figure out.

I've tried various things (VLOOKUP, MATCH etc) but I cannot get it to work. I hope someone can and I hope I've explained this enough.

Thanks for any help here.

[Sam Capricci]

Can you post a sample sheet? (go advanced - then manage attachments and follow the prompts) The sample ought to contain enough information AND expected results for someone to see what is needed.
Are you trying to match on partial fields such as 123 of the ABC123XXY in your example above?

[Glenn Kennedy]

Sounds like a job for Microsoft's fuzzy lookup for Excel. Even better... it's free.

https://www.microsoft.com/en-gb/down....aspx?id=15011

[crl68]

Hi,

Thanks for the quick reply. I have a sample sheet but can't find an advanced link on this page. Can you steer me please...

Cheers

[crl68]

Hi,

I think I've worked it out now...

Rgds

[crl68]

I thought so too but couldn't figure that out either!

[Glenn Kennedy]

This array formula matches on the last 6 digits only. Is this what you want???

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[crl68]

Wow and mega thanks.

Sometimes I sit here and work something out in Excel that others can't and feel good that I was able to help. Then I see something like this that I can't do and I realise how little I know!

Thankfully there are experts like you out there.

Cheers

[Glenn Kennedy]

It's just practice, practice, practice.

you're welcome.