Issue with wilcards in formula

easybpw

Active Member
Joined
Sep 30, 2003
Messages
406
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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,653
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,471
This also works with wildcards:

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

Watch MrExcel Video

Forum statistics

Threads
1,118,273
Messages
5,571,254
Members
412,374
Latest member
Nagelgal
Top