Wildcard search for IF command

**Zyphon** · 06-18-2008, 10:18 AM

Hi guys,

I am having real trouble using the IF command in conjunction with the SEARCH function.

I have a validation drop down list which contain all of the cost codes that I have to use for budgeting purposes. Some of the codes have a higher rate of pay then others and i need to be able to use a an IF command to check the code.

The problem I am having is some of the cods are similar the only difference is that there is a number on the end. For example: BAC10, BAC20, BAC30. What I wanted to know how can I use a wildcard that will only look for the BAC.

Below is the formula I have but it is only half working.

=IF(SEARCH("BAC*",E7),VLOOKUP(K7,Fees,3,0),VLOOKUP(K7,Fees,2,0))

Now this works fine as long as the budget code has BAC in it and it will show the correct rate. However if I use the cost code@ ABC, I get an error: "VALUE#". So for some reason the last part of the VLOOKUP isn't working.

What am I doing wrong?

**EdMac** · 06-18-2008, 01:09 PM

H Michael

What about this as an alternative

=IF(MID(E7,1,3)="BAC",VLOOKUP(K7,fees,3,0),VLOOKUP(K7,fees,2,0))

Regards

**yawnzzzz** · 06-18-2008, 01:15 PM

The Search function returns the location of a string, not whether the string exists. So, what you want to do is surround the code with iserror. Iserror returns true if there's an error or false if there's not.

=IF(iserror(SEARCH("BAC*",E7)),VLOOKUP(K7,Fees,2,0),VLOOKUP(K7,Fees,3,0))

This code will search for BAC, if it finds it then it will go to the false part of the formula:

VLOOKUP(K7,Fees,3,0)

If it doesn't find it, then it will go to the true part of the if statement:

VLOOKUP(K7,Fees,2,0)

You can take it a step farther by searching for "abc", then you would do this:

=IF(iserror(SEARCH("BAC*",E7)),if(iserror(SEARCH("ABC*",E7),"Code for not ABC or BAC",VLOOKUP(K7,Fees,2,0)),VLOOKUP(K7,Fees,3,0))

**Zyphon** · 06-19-2008, 04:55 AM

Thanks guys for your help I really appreciate it. Both methods work really well and I have tried them both so thanks again for the various methods of achieving my goal.

@ yawnzzzz

Thank you for your very descriptive answer and for breaking it down for me to have a better understanding of the formula, that was really helpful.

Again thanks to you both.