Vlookup

Resolved · Medium Priority · Version 2010

Fri 19 Sep 2014, 15:48 Delegate D said...

D has attended:
Excel Introduction course
Excel Advanced course

Vlookup

if the lookup value is from a different sheet to the lookup range, how do we ensure the formula works? i.e. the cell contents may look to same to us but might not be. So quite often, the lookup fails. any solutions?

Mon 22 Sep 2014, 11:50 Trainer Cindy said...

RE: Vlookup

Hello Deepa,

Thank you for your question.

There are a couple of functions that will help, Trim and Clean.
Trim removes all spaces from text except for single spaces between words.
Clean removes all non-printable characters from text.

Here's an example of the trim function nested inside a vlookup:
=VLOOKUP(TRIM(A2),Products,2,FALSE)

Here's an example of the trim and clean functions nested inside a vlookup:
=VLOOKUP(TRIM(CLEAN(A2)),Products,2,FALSE)

I hope that helps. Feel free to response if you want further clarification.

Kind regards

Cindy
Microsoft Office Trainer

Mon 22 Sep 2014, 11:55 Delegate D said...

RE: Vlookup

thank you, Cindy. Will try that and let you know if it doesn't work!

Wed 26 Nov 2014, 11:43 Trainer Jens said...

Will be marked as resolved in 5 days

Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.

Tue 2 Dec 2014: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Paste with Live Preview in Excel 2010

Did you know you can preview what you are about to paste? Here's how to do it.

Copy what you are about to paste
Position the cursor where you want to paste
Right click to display the shortcut menu
Place the cursor over the Paste Options and this will give you a preview of the what you are about to paste.

View all Excel hints and tips