Excel formula to look up text

Microsoft Excel 2007
September 30, 2009 at 10:48:46
Specs: Windows XP Professioanl SP 2, 2Duo CPU E6550 @ 2.33GHz / 3.00GB RAM
I am working with an Excel workbook that contains two sheets.

On the first sheet, I have several columns of information about departments participating in a certain program, including names and email addresses. On the second sheet, I have several columns of information regarding correspondence with the departments, including names and email addresses. I would like to create a conditional format in the cells of one column of sheet 2. Either the column containing the names or email addresses, but preferably the email addresses.

I would like to have the formula look at the email address in the cell and find it in the range on the 1st sheet then, if it is not there, format the cell to fill with a certain color. Basically I would like to say, "if this email is not on the first sheet, fill in with yellow". It looks like I can’t do this because: "You cannot use references to other worksheets or workbooks for Conditional Formatting criteria."

So, if it can’t be a conditional format, I would like to create a column in sheet 2 just to tell me if there is a match. I would like the formula to look in a cell on sheet 2 and find the same email address in a certain column on sheet one & return a "no", if there isn't one, or a “yes” if there is one, or something that would tell me one way or the other.

Is this possible? If so, what is the formula I would use?


See More: Excel formula to look up text

Report •


#1
September 30, 2009 at 12:10:03
re: "You cannot use references to other worksheets or workbooks for Conditional Formatting criteria."

You can reference cells in another sheet if you use a Named Range.

Does that help or do you need more detail to solve your problem?


Report •

#2
September 30, 2009 at 12:20:29
Well, that is helpful to know, but I believe that I also need to know what formula to use.

Thanks so much!


Report •

#3
September 30, 2009 at 12:28:34
Try VLOOKUP...

Name the range that contains the email addresses on Sheet1.

I'll call it emailadds.

Select the range of cells that contain the email addresses on Sheet2. Let's say it's A1:A20. Select that entire range.

Format...Conditional Formatting...Formula is...

=IF(ISNA(VLOOKUP(A1,emailadds,1,0)),TRUE,FALSE)

This will Conditionally Format each cell in the range with it's correct cell address.

The VLOOKUP will look up the email address in Column1 of the range named emailadds. If it doesn't find it, it will return #N/A and that will cause the IF statement to return TRUE and format your cell.


Report •

Related Solutions

#4
September 30, 2009 at 12:46:56
Wonderful! Thank you so much!
I am about to leave for the day, but i will try this tomorrow.
I appreciate your help!

Report •

#5
September 30, 2009 at 12:50:52
No! You can't leave. You have to try it now!

I won't be able to sleep unless I know I solved your problem.


Report •

#6
September 30, 2009 at 12:54:48
Haha! Okay. Hang on . . .

Report •

#7
September 30, 2009 at 12:56:08
I was kidding. Go home!

Report •

#8
September 30, 2009 at 13:07:16
Well, I tried it and nothing happened.
I will have to see if I did something wrong.
I'm not sure I understand the formula.
I am leaving now.
:o)
I'll let you know tomorrow.
Thanks again!

Report •

#9
September 30, 2009 at 13:33:54
For the morning....

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

lookup_value is whatever it is that you are looking up.

In my example, that would be whatever is in A1, for A1, whatever is in A2 for A2, etc.

table_array is the range of cells that VLOOKUP will look up the lookup_value in, and return a value from if the lookup_value is found. VLOOKUP always looks in the first column of the table_array for the lookup_value.

In my example, the table_array is the range I named emailadds.

col_index_num is the column number of the table_array that VLOOKUP will return a value from if it finds the lookup_value in the first column of the table_array.

In my example, I am asking VLOOKUP to return the value in the first column of the table_array, but that doesn't really matter as I'll explain later.

range_lookup tells VLOOKUP whether to look for an exact match or not.

In my example, I used "0" which means I want VLOOKUP to find an exact match.

If VLOOKUP does not find the lookup_value in the first column of the table_array, it will return #N/A.

Excel provides provides a function that allows you to check for a #N/A error which is =ISNA(). It's often used with VLOOKUP and an IF function to prevent the display of the #N/A error. For example,

=IF(ISNA(VLOOKUP(something)),"Not Found",VLOOKUP(something))

What this will do is return "Not Found" if VLOOKUP can't find what it is looking for or it will return the correct value from the table_array if the lookup_value is found. "Not Found" is prettier than #N/A.

So, when using VLOOKUP for Conditional Formatting, we don't really want it to return a value, we just want to know if the lookup_value is found or not.

If the lookup_value is not found, it will return #N/A, which will make the ISNA() function TRUE, which in turn will make the IF statement TRUE which should should format the cell with whatever formats you choose via Conditional Formatting.

You did choose a format to apply to the cells didn't you? If you didn't, nothing will happen even if the IF statement evaluates to TRUE.

BTW...you can test the formula in a cell and it will return TRUE or FALSE depending on whether or not the lookup_value is found. TRUE means the lookup_value was not found.

Hope that helps...



Report •

#10
October 1, 2009 at 11:44:22
YES! That's great! Thank you so much! And thanks for the explanation, too! I sincerely appreciate your time and willingness to share your knowledge.

Report •


Ask Question