lookups

This is a discussion on lookups within the Excel Questions forums, part of the Question Forums category; A spreadsheet contains the following data: columns A-J have name and address info column K contains one of three values: ...

1. A spreadsheet contains the following data:
columns A-J have name and address info

column K contains one of three values: 1) BOTH CDA AND ATL, 2)ATL, or 3) [blank]

columns L and M are blank

column O contains an email address

The challenge:
For each record which has the text "BOTH CDA AND ATL" in column K, columns L and M are to be populated with "Y"; for each record which has the text "ATL" in column K, column L is to be populated with "Y"; for each record which has nothing in column K, column L is to be populated with "Y"

This exercise is performed on several files of varying record counts twice per week. Any help will be greatly appreciated.

2. Welcome to the Board,

hmmmm

in column L, you may aswell just put a whole list of "Y"s as each of the 3 possible choices results in this being returned

that aside, here's the long way :

in COlumn L :

=IF(OR(ISBLANK(K1)=TRUE,K1="ATL",K1="BOTH CDA AND ATL"),"Y","")

in column M :

=IF(K1="BOTH CDA AND ATL","Y","")

3. Wow! Chris, your elegant solution works flawlessly!

Here's the other part of my wish list:
Before this procedure is executed, several other tasks are accomplished with a simple macro: deleting several columns, clearing the contents of one column, and inserting one column.

Chris, I would like your routine to follow these other tasks as part of the macro. Can Excel determine the number of rows in the worksheet and apply the formula accordingly?

Thank you!

4. Can Excel determine the number of rows in the worksheet and apply the formula accordingly?
Yes - how's your VBA? The 'trick' is to determine the size of the range by starting at the top of it, going to the bottom of the sheet & the going back up to the last row - search here for xlUp. One example of the sort of thing you want:

http://www.mrexcel.com/board/viewtop...=18489&forum=2

Post back if you need more,