Multiple Value Possibilities & Conditions

[vickiccc]

I have multiple products that have a certain price associated with them depending on what product group they belong to. Their product group is identified by a 2 letter prefix in the Product Title. I want to say, if the Product Title cell contains "AB", then Price = the AB Group Price. It would be easier to have a column that solely contains the product group associated with that row of data, but for multiple external reasons I cannot do that - the only way to identify the product group is from inside the Product Title.

I tried the following function, which let me alter between Group Prices of AB and DC -but it will not allow me to put any more optional value conditions/results (unless I'm doing something wrong):

=IF(COUNT(SEARCH("AB",'A2')),$E$2,IF(COUNT(SEARCH("DC",'A2'),1),$E$3))

Does anyone know what function would suit this? Thanks in advance.


[TABLE="class: grid, width: 580"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Product Title[/TD]
[TD]Price[/TD]
[TD][/TD]
[TD]Product Group[/TD]
[TD="align: center"]Group Price[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]AB - Product 1[/TD]
[TD][/TD]
[TD][/TD]
[TD]AB[/TD]
[TD="align: center"] $3.45[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]AB - Product 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]DC[/TD]
[TD="align: center"]$7.00[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]AB - Product 3[/TD]
[TD][/TD]
[TD][/TD]
[TD]EF[/TD]
[TD="align: center"] $7.00[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]CD - Product 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]CD - Product 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]CD - Product 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]EF - Product 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]EF - Product 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]EF - Product 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[pgc01]

Hi
Welcome to he board

Try in B2:

=LOOKUP(2,1/SEARCH($D$2:$D$4,A2),$E$2:$E$4)

Copy down

 

[shobolos]

Excel 2010
	A	B	C	D	E
1	Product Title	Price		Product Group	Group Price
2	AB - Product 1	$3.45		AB	$3.45
3	AB - Product 2	$3.45		CD	$7.00
4	AB - Product 3	$3.45		EF	$9.00
5	CD - Product 1	$7.00
6	CD - Product 2	$7.00
7	CD - Product 3	$7.00
8	EF - Product 1	$9.00
9	EF - Product 2	$9.00
10	EF - Product 3	$9.00
Sheet1

Cell Formulas
Range	Formula
B2	=VLOOKUP(LEFT(A2,2),$D$2:$E$10,2)

 

[pgc01]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=VLOOKUP(LEFT(A2,2),$D$2:$E$10,2)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hi shobolos

Just a remark: the formula requires that the product group has exactly 2 characters. If the product group in column D has a variable number of characters, like like "Orange", "Apple", you'll have to adapt the formula.

 

[shobolos]

Fair point.

 

[pgc01]

Notice that my formula is also flawed. It will for ex. match Apple in Pineapple. Let's see what the OP says so that we can tune it (or not).

 

[vickiccc]

Hi all,

This equation worked for some of them. I am getting an #N/A error for a handful of them.
The product group 2 character identifiers I've tried it on thus far:

HP - Product - #N/A
PR - Product - pulls fine
UL - Product - pulls fine
W7 - Product - pulls fine
HB - Product - #N/A
HS - Product - #N/A
O2 - Product - pulls fine
PO - Product - pulls fine

For whatever reason, all my product group identifiers with the letter H do not pull.

 

[vickiccc]

Hi all,

Thanks for your help. Someone recommended I put the ,FALSE at the end and now they are working properly .

 

[GerryZ]

Hi
Welcome to he board

Try in B2:

=LOOKUP(2,1/SEARCH($D$2:$D$4,A2),$E$2:$E$4)

Copy down

@pgc01 I tried your formula and I modified it a bit removing 1/

=LOOKUP(2,SEARCH($D$2:$D$4,A2),$E$2:$E$4)

I belive in a big data set it could be a bit faster!
Bye!