I am trying to create a conditional formula that will look at the first three
characters in a cell and determine a value based on that. Here are four
examples of the data I am looking at and the values I need returned for each:
Data Value returned should be
TAD12345 Ab
TED1263 Ed
TAC25462 Ab CI
TAT9456 Ab TRM
I need a conditional formula that will look at the first three letters in
each of these and return the value based on that. Any suggestions?
You would need to set up a table and then use vlookup and left function.
Let's say your data is in column A
set up a table in column F:G
F as your lookup value and G as your return value:
F G
tad Ab
ted Ed
tac Ab CI
tat Ab TRM
and in B put this in:
=VLOOKUP(LEFT(A1,3),F1:G4,2)
HTH
Google is your best friend!
=IF(LEFT(A1,3)="TAD","Ab","")
Replace "" with other IF statements as appropriate.
"SMC0890" wrote:
> I am trying to create a conditional formula that will look at the first three
> characters in a cell and determine a value based on that. Here are four
> examples of the data I am looking at and the values I need returned for each:
>
> Data Value returned should be
> TAD12345 Ab
> TED1263 Ed
> TAC25462 Ab CI
> TAT9456 Ab TRM
>
> I need a conditional formula that will look at the first three letters in
> each of these and return the value based on that. Any suggestions?
>
>
Hi,
I would first create a table of two columns and as many rows as you need. With your data mine looks like this.
TAD Ab
TED Ed
TAC Ab CI
TAT Ab TRM
Then I name the table "YourTable" (you can call it what you want to) INSERT>NAME>DEFINE
Then the following formula works for me, Assuming your data starts in cell A1 (change to suit)
=VLOOKUP(LEFT(A1,3),YourTable,2,FALSE)
HTH
Casey
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks