I am trying to find if a value in a column exisits within another column, but need to return only one value. I have tried using lookup but If I try to check multiple values it spills over in multiple columns. I simply just want a true or false when comparing a a set of value to another.

Here is a picture of my data and what I am trying to do. I am trying to make sure that once I use a job in my right column that it wont be used again which I need some function to make sure that the currently used jobs are not used again. So I want to check the column of all jobs and the previous jobs that have been used to make sure I wont repeat a job. I know I can do this with a solver matrix but am not able to.

1

1 Answer

You can do this using data validation and custom formulas to prevent entry of duplicate jobs or jobs that aren't in the other list. I'm going to presume the first column (j(job)) is column A and the one near the right (job) is column L.

Only allow jobs in L that are already in the list in A

Select all of column L (or some range you care about like L2:l50 or whatever) and click on Data Validation in the Data ribbon.

Change the "Allow" drop-down to "Custom" and use the following formula:

=NOT(ISERROR(MATCH(L1,A:A,0)))

Note how it reference L1. That's because it's the first cell in the range I've selected. If you select a different range, that cell reference would need to be changed to whatever the first cell in that range is. IE, if you pick L2:L100, the formula should reference L2. If you want, you can also set some custom error message:

Only allow one of each entry in L

Same thing, different formula:

=COUNTIF(L:L,L1)<2

Do Both at Once

Same thing, different formula (combines the other two with an AND() function):

=AND(NOT(ISERROR(MATCH(L1,A:A,0))),COUNTIF(L:L,L1)<2)

Note: All of these will only prevent incorrect entry at the time the data is entered. If you want to go back and highlight all the cells that already have data in order to find those that don't meet this criteria, it would be a similar solution using conditional formatting.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy