vLOOKUP SOMETIMES RETURNS NO MATCH

Ian Smith 2017

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.

Paul Newcome

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.

Paul Newcome

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.

Ian Smith 2017

That's perfect Paul, thank you for your help. Problem solved by using false / 0

Paul Newcome

Happy to help. 👍️

LizHHC01

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?