VLookup - Showing the values which DO NOT Match
 

Hi

Apologies if this has already been posted but I could not find this question
anywhere.

I have created a Vlookup formula which works exactly how I'd expect it to
(shows value when a match is found between 2 columns and N/A when it doesn't)
.. However, what I need to determine is when it says N/A why is it showing
it. In other words I want it to show me in the results table or even
highlight the incorrect value in the original table using conditional
formatting so I can analyse this information and take the necessary steps to
rectify it.

I hope this makes sense!

Thanks in advance for any help/ advice.

VLookup - Showing the values which DO NOT Match
 

Arsenal Lady 09 wrote:
Hi

Apologies if this has already been posted but I could not find this question
anywhere.

I have created a Vlookup formula which works exactly how I'd expect it to
(shows value when a match is found between 2 columns and N/A when it doesn't)
. However, what I need to determine is when it says N/A why is it showing
it. In other words I want it to show me in the results table or even
highlight the incorrect value in the original table using conditional
formatting so I can analyse this information and take the necessary steps to
rectify it.

I hope this makes sense!

Thanks in advance for any help/ advice.

This is the standard syntax for VLOOKUP():

=VLOOKUP( lookup_value , table_array , col_index_num , range_lookup )

#N/A being returned would indicate that "lookup_value" is not found in the first
column of "table_array". Use can your exact VLOOKUP(), wrapped in ISNA(), for
conditional formatting in the "lookup_value" cell.

What would you propose to highlight in "table_array" if "lookup_value" is not found?

VLookup - Showing the values which DO NOT Match
 

Glenn wrote:
Arsenal Lady 09 wrote:
Hi
Apologies if this has already been posted but I could not find this
question anywhere.

I have created a Vlookup formula which works exactly how I'd expect it
to (shows value when a match is found between 2 columns and N/A when
it doesn't) . However, what I need to determine is when it says N/A
why is it showing it. In other words I want it to show me in the
results table or even highlight the incorrect value in the original
table using conditional formatting so I can analyse this information
and take the necessary steps to rectify it.
I hope this makes sense!

Thanks in advance for any help/ advice.

This is the standard syntax for VLOOKUP():

=VLOOKUP( lookup_value , table_array , col_index_num , range_lookup )

#N/A being returned would indicate that "lookup_value" is not found in
the first column of "table_array". Use can your exact VLOOKUP(),
wrapped in ISNA(), for conditional formatting in the "lookup_value" cell.

What would you propose to highlight in "table_array" if "lookup_value"
is not found?


=SUBSTITUTE(my_response,"Use can","You can use",1)

VLookup - Showing the values which DO NOT Match
 

Excel humor is fun! :-)

Thinking perhaps we are looking to do a VLOOKUP on the original table to
find values that don't match the new table? Just swap the vlookup to find the
#N/A values...

"Glenn" wrote:

Glenn wrote:
Arsenal Lady 09 wrote:
Hi
Apologies if this has already been posted but I could not find this
question anywhere.

I have created a Vlookup formula which works exactly how I'd expect it
to (shows value when a match is found between 2 columns and N/A when
it doesn't) . However, what I need to determine is when it says N/A
why is it showing it. In other words I want it to show me in the
results table or even highlight the incorrect value in the original
table using conditional formatting so I can analyse this information
and take the necessary steps to rectify it.
I hope this makes sense!

Thanks in advance for any help/ advice.

This is the standard syntax for VLOOKUP():

=VLOOKUP( lookup_value , table_array , col_index_num , range_lookup )

#N/A being returned would indicate that "lookup_value" is not found in
the first column of "table_array". Use can your exact VLOOKUP(),
wrapped in ISNA(), for conditional formatting in the "lookup_value" cell.

What would you propose to highlight in "table_array" if "lookup_value"
is not found?


=SUBSTITUTE(my_response,"Use can","You can use",1)
.