VLOOKUP to find a keyword

Resolved · Medium Priority · Version 2016

Fri 13 Apr 2018, 09:50 Delegate Kay said...

Kay has attended:
Excel Advanced course
Excel Advanced - Formulas & Functions course

VLOOKUP to find a keyword

Hi,

Just need some advice on whether it is possible to use Vlookup to find a partial match through "keyword" search?

I have 2 separate spread sheets which have different description for the same thing and want to match them through Vlookup. Is this possible?

Any guidance will be greatly appreciated

Many thanks

Fri 13 Apr 2018, 10:19 Trainer Jens said...

RE: VLOOKUP to find a keyword

Hi Kay,

Thank you for the forum question.

Is there any pattern in the keywords?? If there is you can nest text functions (Left, Right, Mid, Search, Len) in the lookup value in the Vlookup.

If it is possible you can send me the file and I can have a look at it.

info@stl-training.co.uk

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Fri 13 Apr 2018, 17:25 Delegate Kay said...

RE: VLOOKUP to find a keyword

Hi Jen,

Thanks for your prompt reply

Its not possible for me to share the spread sheet.

As an example:

One spread sheet the description can be written as:

"Walter G Maxwell"

however in the other this appears as

"Walter Maxwell"

so both spread sheet have a key word that match

The key word would need to be set as dynamic so it can be changed for each line.

Hope that clarifies

Thanks

Fri 13 Apr 2018, 18:48 Trainer Jens said...

RE: VLOOKUP to find a keyword

Hi Kay,

You have a special lookup function which may do what you need. The Fuzzy Lookup function is a free add-in you can download on Microsoft's website.

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

You can set the accuracy to tell the lookup how accurate you want it to be.

If you have experience with Power Query you can clean one of the lists so you have the same information in both lists.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Mon 16 Apr 2018, 07:06 Delegate Kay said...

RE: VLOOKUP to find a keyword

Thanks for the update Jen

I don't have experience with Power Query but will look into the Fuzzy lookup function add in.

Many thanks
Kay

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Use the SUBTOTAL function in Excel

You can create subtotals in your spreadsheet using the SUBTOTAL function, which looks like this:

=SUBTOTAL(9,cell:cell)

9 represents the function being used (SUM), followed by the range of cells the function is operating on.

The neat thing about using the Subtotal function is that if you have used it several times in the same column or row, clicking on the AutoSum button at the end of the column or row will make Excel add only the results of cells containing the Subtotal function in that column or row.

View all Excel hints and tips