• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 145
  • Last Modified:

MS Excel - setting a column value based on another

I have an excel spreadsheet that has any columns, one of which column(T) is a role value.  I want column (U) to be a value based on the content of column T

example
T Contains Civilian then S = Civilian
T Contains SLED then S = S&L
T Contains Defense then S = Defense/Fedsec
T Contains Healthcare then S = Healthcare
T Contains CS then S = CS/Intel
0
Matt Pinkston
Asked:
Matt Pinkston
  • 3
  • 3
1 Solution
 
Steven HarrisPresidentCommented:
Are these all of the values?  What format are they in originally (in column T)?  DO you have a sample workbook?
0
 
Matt PinkstonAuthor Commented:
It is all general text... Yes that should probably do it for total.
0
 
NBVCCommented:
Maybe:

=LOOKUP(T2,{"Civilian","CS","Defense","Healthcare","Sled"},{"Civilian","CS/Intel","Defense/Fedsec","Healthcare","S&L"})

Note, the first array is in alphabetically ascending sorted order, the second array contains the corresponding strings to return.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Matt PinkstonAuthor Commented:
did not work..

I tried I on a row that had ES XXXX CIVILIAN MANAGER in the lookup columns and it returned Defense/Fedsec
0
 
NBVCCommented:
In that case try:

=LOOKUP(9.9999E+307,SEARCH({"Civilian","CS","Defense","Healthcare","Sled"},T2),{"Civilian","CS/Intel","Defense/Fedsec","Healthcare","S&L"})
0
 
Matt PinkstonAuthor Commented:
what is the 9.9999E+307,


=LOOKUP(9.9999E+307,SEARCH({"Civilian","CS","Defense","Healthcare","Sled"},T2),{"Civilian","CS/Intel","Defense/Fedsec","Healthcare","S&L"})
0
 
NBVCCommented:
It is the largest number that Excel recognizes.  The LOOKUP() is looking for that number in the results of the the SEARCH() function.  It obviously won't find it, so it returns the last number that is smaller than the lookup value, this representing the last time it found a match in the SEARCH() function.  And therefore returns the corresponding item from the last array.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now