vlookup
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. |
vlookup
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. |
vlookup
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 wrote: 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. -- Dave Peterson |
vlookup
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. "TomPl" wrote: 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. |
vlookup
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. "TomPl" wrote: 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. |
vlookup
Thanks for the response. I tried that first and got n/a#. Even made sure to
format columns as text. "Dave Peterson" wrote: 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 wrote: 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. -- Dave Peterson |
vlookup
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. |
vlookup
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 wrote: Thanks for the response. I tried that first and got n/a#. Even made sure to format columns as text. "Dave Peterson" wrote: 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 wrote: 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. -- Dave Peterson -- Dave Peterson |
vlookup
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" wrote: 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 wrote: Thanks for the response. I tried that first and got n/a#. Even made sure to format columns as text. "Dave Peterson" wrote: 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 wrote: 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. -- Dave Peterson -- Dave Peterson |
vlookup
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. bduncan wrote: 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" wrote: 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 wrote: Thanks for the response. I tried that first and got n/a#. Even made sure to format columns as text. "Dave Peterson" wrote: 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 wrote: 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. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
All times are GMT +1. The time now is 12:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com