Issue with wilcards in formula

easybpw

Active Member
Joined
Sep 30, 2003
Messages
407
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Hello and thanks in advance for the help.

I'm using Microsoft Office Professional 2013 and am having issues with wildcards in the formula below. Maybe I am not understanding things clearly.
I am trying to say that if F310 contains "Staff Charge" to put "1614" in the cell with the formula. And if it doesn't contain "Staff Charge" to do the lookup. The lookup works great without the IF statement I added. But I need the lookup to work only if F310 doesn't contain "Staff Charge". Yes, there is text before and after "Staff Charge" and this text is not constant. What am I missing? Are wildcards not possible with the formula I'm using? If not what is an alternative?


=IF(F310="*Staff Charge*","1614",LOOKUP(3,1/('JE Mapping'!$A$2:$A$150=Detail!$C310)/('JE Mapping'!$B$2:$B$150=Detail!$E310)/('JE Mapping'!$C$2:$C$150=Detail!$F310),('JE Mapping'!$D$2:$D$150)))
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
You cannot use wildcards like that, try
Excel Formula:
=IF(ISNUMBER(SEARCH("Staff Charge",F310)),"1614",...
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,494
This also works with wildcards:

Excel Formula:
=IF(COUNTIF(F310,"*Staff Charge*"),"1614", . . .
 

Watch MrExcel Video

Forum statistics

Threads
1,119,224
Messages
5,576,830
Members
412,749
Latest member
BlakeVanderMeer
Top