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.

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

<tbody>
</tbody>

 

[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]

Worksheet Formulas Cell Formula B2 =VLOOKUP(LEFT(A2,2),$D$2:$E$10,2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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!