vLOOKUP SOMETIMES RETURNS NO MATCH
Hi Everyone,
Thanks to some very helpful advice on here I've amended our company quotations sheet to reference a credit checking sheet and then show if a client has been credit checked and what their limit is.
99% of the time this works perfectly, from time to time however it returns #NO MATCH this is without exception for clients whose names start with A!
They are all using exactly the same vlook up formulas and refer to the same sheet. Has anyone come across this before??
Credit check vlookup is =VLOOKUP(Customer@row, {Original Data sheet Range 3}, 2, 1)
Credit limit vlookup is =VLOOKUP(Customer@row, {21/23 - Original Data sheet range4}, 2, true)
Credit limit exceeded is a simple formula =IF(AND(Value@row > [Credit Limit]@row, [Credit Checked?]@row = 1), 1, 0)
And the sheet they reference is as follows
Other than asking clients to change their trading names, I'm at a loss how to fix it.
Best Answer
-
Try changing the final argument of the VLOOKUPs to false. "true" (or 1) will only search for an approximate match whereas "fasle" (without the quotes) searches for an exact match.
I also suggest looking in to an INDEX/MATCH instead of a VLOOKUP in general because it allows for MUCH more flexibility and provides better performance and management overall.
=INDEX({Only The Column You Want Pulled}, MATCH(Customer@row, {Only The Customer Column}, 0))
Since you are referencing each column as individual ranges, the order of the columns in the reference table doesn't matter. They can be in any order and can be rearranged on the source sheet as needed without worrying about breaking any formulas looking at them. It also means that you don't have to reference every column in between the pull column and the match column which can mean less cells being referenced which can increase sheet performance.
Answers
-
Try changing the final argument of the VLOOKUPs to false. "true" (or 1) will only search for an approximate match whereas "fasle" (without the quotes) searches for an exact match.
I also suggest looking in to an INDEX/MATCH instead of a VLOOKUP in general because it allows for MUCH more flexibility and provides better performance and management overall.
=INDEX({Only The Column You Want Pulled}, MATCH(Customer@row, {Only The Customer Column}, 0))
Since you are referencing each column as individual ranges, the order of the columns in the reference table doesn't matter. They can be in any order and can be rearranged on the source sheet as needed without worrying about breaking any formulas looking at them. It also means that you don't have to reference every column in between the pull column and the match column which can mean less cells being referenced which can increase sheet performance.
-
That's perfect Paul, thank you for your help. Problem solved by using false / 0
-
Happy to help. 👍️
-
Hi there having same issue- receiving the NO MATCH error when I know the info is in my reference sheet:
=VLOOKUP([Contract #]@row, {Contract ID#'s Range 2}, 1, false)
Can I not use a Vlookup in a dropdown field?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!