Wildcards in IF statements with lookup lists.

[fobistee]

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

 

[sanrv1f]

Welcome to the board,

If I understood the requirement correctly, this could be a solution

Excel Workbook
	G	H	I	J
4	B1	B2	C1	B3
Sheet1

Excel 2003

Excel Workbook
	A	B
7	B1	34
8	B2	71
9	B3	67
10	B4	92
11	C1	55
12	C2	52
13	C3	3
14	C4	11
15	D1	42
16	D2	17
17	D3	46
Sheet1

Excel 2003

Excel Workbook
	J	K
7	Result	172
Sheet1

Excel 2003

Cell Formulas
Range	Formula
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]

Does the following behave as you expect...

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

assuming that NEWSHIFTS consists of just 3 columns?

 

[fobistee]

Thank you very much.

It looks like that has worked.