Vlookup

**mfkdghd** · 07-14-2009, 05:49 PM

Hey there,

I'm having trouble to get VLOOKUP to work. Basically I need to look up Management Teir information from Sheet1 onto Main sheet as per the file attached. Both RecordID columns are Text so they should work right? But VLOOKUP returns #NA error. Anyone knows what's going on here?

Any reply greatly appreciated

**Paul** · 07-14-2009, 06:01 PM

Hi mfkdghd,

First, your text 'Emp No' values on Sheet1 all have five spaces after them. Remove those five spaces and your VLOOKUP functions will start to work.

Second, your VLOOKUP functions on Main are not using a static table range. Notice how the table to search changes as you go down the rows. To fix this, go to B2 and use:

=ISERROR(VLOOKUP(A2,Sheet1!$A$2:$B$81,2,FALSE))

Fill that down to B26. Then in C2 use:

=IF(ISERROR(VLOOKUP(A2,Sheet1!$A$2:$B$81,2,FALSE)),"Error here",VLOOKUP(A2,Sheet1!$A$2:$B$81,2,FALSE))

Fill that down to C26. Notice the $'s in the table range, this will keep those cell references static down the entire column of formulas.

**mfkdghd** · 07-14-2009, 06:45 PM

Thank you so much Paul for your prompt yet helpful response! Really appreciate it. I am currently working to fix both.

I hate to ask again but I am having trouble to trim off the trailing spaces of those Text values. '=Trim(A2)' stays at itself literally whilst I am expecting whatever in cell A2 but trimmed. If I use '=Trim(" Fax")' it returns "Fax" as expected. Can you please advise what's wrong with range reference in Trim()?

Kind regards
Frank

**martindwilson** · 07-14-2009, 07:35 PM

easiest way is to select all values in col a sheet 1 (but not header row)
then
find/replace
find .... just hit space bar once
replace ... leave it blank
replace all

**mfkdghd** · 07-14-2009, 08:31 PM

Yup Martin find+replace is a much better way to do that than Trim().

I also managed to get Trim() to work. The trick is to set the cell where =Trim() is located, 'General' rather than 'Text'. So apparently Trim() likes 'General' cell type alot better(Text seems to even treat clearly a formulae literally?

)

Thanks Martin and Paul for being so helpful!

Regards
Frank