Clean function combined with VLOOKUP

**Nathalie1974** · 02-23-2012, 02:31 PM

I've never used the CLEAN function combined in a VLOOKUP - but I will need to today!

=VLOOKUP(J17,Table,2,FALSE)

I am getting and error of #N/A for my Vlookup formula and the reason is that J17 is a Vlookup and the format is different. So basically I would like to clean J17 and continue on with the VLookup as is.

=VLOOKUP(CLEAN(J17,Table,2,FALSE) is not working. I'm assuming that I need to tell "clean" what to clean, but how?

Thanks a lot!

**JBeaucaire** · 02-23-2012, 02:32 PM

What are the two strings that are mismatched? Can you show us in a sample workbook the problematic data vs search string?

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

**Nathalie1974** · 02-23-2012, 02:50 PM

I'm not sure that I understand the question...

Basically I want to lookup 2011 performance ratings in J17. J17 is an SAP report. Table is the performance table so S=100%

Vlookup(Performance rating-J17-"S",Performance table-"S" column 1 and 100% in Column 2, 2-100%, FALSE)

I think the SAP data is the issue. If I copy and paste an S, my Vlookup function works.

**integra** · 02-23-2012, 07:53 PM

Nathalie
check your criteria vs. your download data. based on your comment, i think your data has "differen" value than your criteria. sometime it has extra space(s) in your SAP data. Try function "Trim" or "abs" at J17.