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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

sanrv1f

MrExcel MVP
Joined
Jan 1, 2009
Messages
3,474
Office Version
  1. 2016
Platform
  1. 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,206
Does the following behave as you expect...

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

assuming that NEWSHIFTS consists of just 3 columns?
 

Watch MrExcel Video

Forum statistics

Threads
1,132,996
Messages
5,656,295
Members
418,293
Latest member
michaelkellyfrombelfast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top