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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

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,210
Does the following behave as you expect...

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

assuming that NEWSHIFTS consists of just 3 columns?
 

Forum statistics

Threads
1,176,348
Messages
5,902,635
Members
434,983
Latest member
JenniferNash

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