Formula help with vlookup or index match

[blaineschultz]

I have a master data source sheet 'msf12' tht contains a column with the names of companies. i have a second sheet that contains a list of all the companies names, their region, zip code ect. sheet name 'companies'. I am trying to create a formula that will look at the companies name and then generate their respective region served in a new column on the same sheet using the reference sheet 'companies' however, my results have either been accurate up to around row 100 when the regions column starts giving row references that are off by 1-3 rows. I tried to combat this by using index match but in that case i only get a few regions and the rest of the cells display N/A. 'companies' is sorted in ascending order both fields are text values i feel like i have tried everything. here is a quick example.

My main source that has the companies name- I need to create a column that shows the corresponding region. Right now I have name/Service type

[TABLE="width: 500"]
<tbody>[TR]
[TD]Martz Transport - Wikes - Sacramento
[/TD]
[TD]CTC[/TD]
[/TR]
[TR]
[TD]Martz Transport - Wikes - Vegas[/TD]
[TD]CTC

[/TD]
[/TR]
[TR]
[TD]MTR Western
[/TD]
[TD]CTC[/TD]
[/TR]
[TR]
[TD]Abbott Trails
[/TD]
[TD]CTC[/TD]
[/TR]
[TR]
[TD]Mlaker Trans[/TD]
[TD]CTC[/TD]
[/TR]
</tbody>[/TABLE]


The reference sheet is like this.. Company name/zip/region(the cell i need) is in col E

[TABLE="width: 500"]
<tbody>[TR]
[TD]Martz Transport - Wikes - Sacramento[/TD]
[TD]94445[/TD]
[TD]Southern Cal
[/TD]
[/TR]
[TR]
[TD]Martz Transport - Wikes - Vegas[/TD]
[TD]74665[/TD]
[TD]Western[/TD]
[/TR]
[TR]
[TD]MTR Western[/TD]
[TD]37382[/TD]
[TD]Great Lakes[/TD]
[/TR]
[TR]
[TD]Abbott Trails[/TD]
[TD]83727[/TD]
[TD]NY Metro[/TD]
[/TR]
[TR]
[TD]Mlaker Trans[/TD]
[TD]88888[/TD]
[TD]Southeast[/TD]
[/TR]
</tbody>[/TABLE]


Right now i have this formula and it works for the companies up until Martz..
=LOOKUP([@operator],'Bus region Source1'!$A$2:$A$243,'Bus region Source1'!$E$2:$E$243)

Any way to make this work??

 

[Gideon1973]

I notice that sometimes VLookups will fail whenever the formats/data types aren't the same between the source and lookup columns. For example, say the value 12345 is stored as number on one sheet, but as text in the other, the VLookup will fail.

You might want to check formats and "equalize" them, i.e., make them all the same format/data type.

Not sure if that's the problem, but you may want to check, especially if some rows look up okay and others don't...

 

[NBVC]

Try VLOOKUP so that you can find exact matches.

e.g.

=VLOOKUP([@operator],'Bus region Source1'!$A$2:$E$243,5,FALSE)

 

[blaineschultz]

Try VLOOKUP so that you can find exact matches.

e.g.

=VLOOKUP([@operator],'Bus region Source1'!$A$2:$E$243,5,FALSE)

Thanks! that worked for most of the values. However I am getting #N/A for a good amount of the columns still. I checked and the cells are both formatted to text and the company name cells match perfectly. Any other ideas what is causing this?

 

[Gideon1973]

Could there be "phantom" characters in one of the values (either the source or lookup target)? Like trailing spaces, control characters (like tabs, carriage returns, etc.)?

ADD:
You can also try double-clicking in one of the cells which is returning an #N/A and then clicking off of it. If the lookup then works, there's a data type formatting problem.

 

[blaineschultz]

Your the man! The source had some phantom spaces. FIXED

 

[Gideon1973]

You're welcome.

Cheers!