Selecting the Correct Value

Topic

New Reply

I’m using Excel 2007 and I need to compare some values.

If “My Code”=L110 AND “Client Code” falls in the range Z004-Z009 THEN it’s valid. If it falls outside the range, then it’s an error.

My Code……….Client Code
L110…………….Z004
L110…………….Z005
L110…………….Z062

How would I write this formula?

Thank you for your help!

Reply | Quote

Replies

Hello – What should happen if “My Code” does not equal L110 ?

If value of “My Code” does not matter, try the following formula in Column C to test for “Client Code” that falls in the range Z004-Z009.

=IF(AND(B3>=”Z004″,B3<="Z009"),"Valid","Error")

Assumes "My Code" is in Column A and "Client Code" is in Column B

Reply | Quote

Thank you Tim for your response. So, the formula gets more complicated if My Code doesn’t equal L110. I actually have several My Codes and Client Codes. My Code may be L110, L120, L130…L250 and there are ranges for each Client Code that should match with the corresponding My Code.

For example:

My Code……….Client Code Range
L110…………….Z004-Z009
L120…………….Z010-Z012
L130…………….Z013-Z015

What I’m trying to accomplish is some way to draw my attention to My Code so that if the Client Code is outside the range, I can see that there’s an error and correct it.

I hope this makes sense.

Thank you again.

Reply | Quote

Hello – The attached workbook should do what is needed.

There are two tabs:
Data – Would be all of your data as it is currently laid out (I improvised and added Column C).
Valid Codes – Would contain all of the valid code combinations in Columns B and C. Please note the formula in Column A.

Range Name of “Codes” includes all of the Valid Codes info.

The results on the Data tab gives #NA for all invalid code combinations.

Hope this is what you need.

Tim

Reply | Quote

Hello – You could also have the invalid cells show in color by using Conditional Formatting. This will automatically highlight cells with errors.
See attached.

Tim

Reply | Quote

Tim:

Your formulas worked perfectly. Thank you so much!

Caroline

Reply | Quote