Wildcard search for IF command

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.

Please Login or Register  to view this content.

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?

H Michael

What about this as an alternative

Please Login or Register  to view this content.

Regards

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.

Thanks for the feedbacK

Regards