Remove extra spaces from the lookup value with TRIM in Excel
Generally, you can use the VLOOKUP function to lookup a value and return the matched value. However, if there are some extra spaces, such as leading spaces or trailing spaces in cells, the formula will return an error value #N/A even if there is a value matched as below screenshot shown. Here this tutorial provides some tricks on solving this job.
Unbelievable! A tool changes the way on editing and browsing multiple Office documents.
Open files in multiple windows
Open files in one tabbed window with Office Tab
In this case, you can combine the VLOOKUP and TRIM functions to lookup value ignoring extra spaces.
Select a blank cell that you will place the result, type this formula
HR is the lookup value, you also can directly use the cell reference which contain the lookup value, such as =VLOOKUP(F2,TRIM($B$3:$C$6),2,0)
B3:C6 is the table array you use, 2 is the column index you want to return.
Press Shift + Ctrl + Enter key to return the match value.
If you want to use the general VLOOKUP formula =VLOOKUP(F2,$B$3:$C$6,2,0), you need to remove the extra spaces from the cells firstly.