vlookup

[bduncan]

Why is my vlookup formula returning the number in the row above what it
should be? My sheet looks like this;
H K
39265 CLAMP, RAIL SPLICE =vlookup(h3, costs,2).


sheet 2
C D
39265 CAM LINKS 2 POSTION FOR FR150 $150
39265 CAM LINKS PREFORM INSERT HOLDE $700
39265 CLAMP, RAIL SPLICE, 3" $32.24

My formula keeps returning $700 instead of the $32.24. If I delete the $700
it returns $150. I've had the issue before where my cell range is wrong but
this time I haven't been able to figure it out.

 

[TomPl]

Unless the match is exact your formula will return the the first lookup that
is smaller.
=vlookup(h3,costs,2,false) will return only exact matches.

if you delete , 3" from the end of your lookup value it will give you the
correct answer.

You might consider reviewing the help file for vlookup.

 

[Dave Peterson]

Since you're matching on text, I bet you want an exact match.

Try:

=vlookup(h3, costs,2,False)

That 4th parm (false) specifies that you want an exact match.

 

[bduncan]

Thanks for the quick respone. I saw that in the help file, but what threw me
off was why wouldn't it find $150 first since it comes before $700.

Unfortunately our accounting software didn't export the names the same on
both sheets. I actually had to seperate columns with formulas and then
combine 2 columns to find something close in relation on both sheets.
This is the first time I've tried using true instead of false in my formula.

 

[bduncan]

Something else I forgot to mention. This is at the top of my sheet so if I
delete the 2 rows above the value I'm looking for I get an n/A.

 

[bduncan]

Thanks for the response. I tried that first and got n/a#. Even made sure to
format columns as text.

 

[TomPl]

If you don't get an exact match it will return N/A.
I suspect the only way you will get this to work is if you find a way to get
the values on the two sheets to be exact. Maybe you can use a part number or
some other identifier.

 

[Dave Peterson]

That means that there isn't an exact match.

This fuzzy matching could be difficult to implement.

Any chance you could just take the first few characters (say 15 characters) and
match on that?

=vlookup(left(h3,15)&"*",costs,2,false)

 

[bduncan]

Thanks for the formual that is what I needed. What does the &"*" mean. I'm
guessing it has something to do with only matching part of the cell?

 

[Dave Peterson]

Some of excel's functions support wildcards.

The asterisk (*) represent any set of characters.

You could also use a question mark (?) that would represent a single character.

Thanks for the formual that is what I needed. What does the &"*" mean. I'm
guessing it has something to do with only matching part of the cell?