Feb 04 2020 11:01 AM
Hi
Needing some help with identifying what type of grant I have on worksheet using a formula. Each grant has its own project code, the format or letters dictate what category of award it its. For example, the followung table -
Column in Yellow is my source column, the column in Green is what I want to show each award represents or Category. The text in RED is to showing you the key letters that help identify that type of award. I have tried FIND, SEARCH and LEFT or combination of and I am stuck. Any suggestions, or links to Excel functions, welcome. I want to use a formula rather than any VBA as the sheet is used my several people and adding a macro function will not work in my situation.
Chris
Feb 04 2020 11:16 AM
SolutionFeb 04 2020 12:47 PM
Many thanks, I can now where I have been going wrong, I have been adding the OR function to my IF statement. Your solution works great thanks.
Chris
Aug 10 2020 02:09 AM
= IFS(
LEFT([@CODE],1)="D", "GEN GRANT",
LEFT([@CODE],2)="RS", "INTERNAL GRANT",
TRUE, "KEY GRANT" )
Same idea but uses Table referencing and the IFS function.
Aug 13 2020 01:27 AM
While I think of it ...
It is possible to search for slightly more complex substrings using COUNTIFS and wild card searches
= IFS(
COUNTIFS([@Code],"RS*"),"INTERNAL GRANT",
COUNTIFS([@Code],"D*"),"GEN GRANT",
TRUE, "KEY GRANT" )
Since the formula searches only a single cell, the count will be 0 or 1.
An advantage of the table is that the formula range extends as data is appended to the table.
One less manual operation is one less opportunity for error.
Feb 04 2020 11:16 AM
Solution