Wildcards in IF statements with lookup lists.

fobistee

New Member
Joined
Jul 30, 2010
Messages
2
Hi,

I was wondering if anyone can help me with my IF function.
IF(G4="B",VLOOKUP(G4,NEWSHIFTS,3),0))+IF(H4="B",VLOOKUP(H4,NEWSHIFTS,3),0)

The above formula continues for quite a few repeats.

I want it to find any cell with a B* and then look at the newshifts vlookup and add the numbers together.

However I can only get it to work when I change the "B" to "B1" or "B2" etc for the corresponding cell because the *, ? and ~ wildcards don't work.

Please help :)

Thanks Emma
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

sanrv1f

MrExcel MVP
Joined
Jan 1, 2009
Messages
3,474
Office Version
2016
Platform
Windows
Welcome to the board,

If I understood the requirement correctly, this could be a solution
Excel Workbook
GHIJ
4B1B2C1B3
Sheet1
Excel 2003
Excel Workbook
AB
7B134
8B271
9B367
10B492
11C155
12C252
13C33
14C411
15D142
16D217
17D346
Sheet1
Excel 2003
Excel Workbook
JK
7Result172
Sheet1
Excel 2003
Cell Formulas
RangeFormula
K7=SUMPRODUCT(SUMIF($A$7:$A$17,G4:J4,$B$7:$B$17),--(LEFT($G$4:$J$4,1)="B"))


Change cell ranges to suit your need
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Does the following behave as you expect...

=SUMPRODUCT(SUMIF(NEWSHIFTS,(H4:G4)&"*",NEWSHIFTS))

assuming that NEWSHIFTS consists of just 3 columns?
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,397
Messages
5,511,111
Members
408,825
Latest member
Goncalves74

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top