# IF function



## joty19 (Dec 28, 2022)

I have data in two sheet Short List and Badge. I am trying to figure out a formula that says if the author in ShortList Column D is found in Badge Column B2:B69 then it is a "detective" 

If the author in ShortList Column D is found in Badge Column E2:E250 then it is "patrol"

If the author in ShortList Column D is found in Badge Column H2:H32 then it is "PST"

If the author in ShortList Column D is found in Badge Column K2:K5 then it is "TRU"

=IF(COUNTIF(Badge!$E$2:$E$250,ShortList!D2),"Patrol",IF(COUNTIF(Badge!$B$2:$B$69,ShortList!D2),"Detective"))

When I continue this function for PST and TRU I receive "FALSE" in the cell. The only difference is that their badge numbers are alphanumeric ie P95 whereas the patrol and detective badge number are only numeric. Is there a way around this?


----------



## Fluff (Dec 28, 2022)

Hi & welcome to MrExcel.
How about

```
=IF(COUNTIF(Badge!$E$2:$E$250,D2),"Patrol",IF(COUNTIF(Badge!$B$2:$B$69,D2),"Detective",IF(COUNTIF(Badge!$H$2:$H$69,D2),"PST",IF(COUNTIF(Badge!$K$2:$K$69,D2),"TRU",""))))
```


----------



## joty19 (Dec 28, 2022)

Fluff said:


> Hi & welcome to MrExcel.
> How about
> 
> ```
> ...



Hi, thank you for your reply. I copy and pasted the formula you sent and there are improvements. Now the alphanumeric badge number cells return blank. I've attached a little snippet.


----------



## Fluff (Dec 28, 2022)

Can you post your actual data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.


----------



## joty19 (Dec 29, 2022)

Fluff said:


> Hi & welcome to MrExcel.
> How about
> 
> ```
> ...


The function worked, it turns out the PST column had several spaces after the number so we had to use the trim function. Once that was correct your formula worked. Thank you!


----------



## Fluff (Dec 29, 2022)

Glad it's sorted & thanks for the feedback.


----------

