vlookup exact AND partial match

[DrewP]

I cannot seem to write a vlookup formula that provides what I need. I am looking to search Products in A8-11 and return price in B8-11. I need to match A2 AND B2 in this search. As you can see, partial results should show for A8 and return the corresponding price in cell B8. Can you help?

PH_Sales_Pivot_View (32).xls
	A	B	C
1	Product	subscription	price?
2	10 Gym	2 years
3	blockbuster	9 months
4	Olive Garden	n/a
5
6
7	Product	Price
8	2 year 10 gym membership	500
9	1 year blockbuster card	50
10	3 year 10 gym membership	100
11	Olive Garden gift card	10
Sheet2

 

[etaf]

try
=VLOOKUP("*"&A2&"*",$A$8:$B$11,2,FALSE)

BUT it will find the first one in the list - if you have this more then once

for example anything with 10 gym in the first wil be found

9 months does no match anything nor does N/A
so how will the A and B - be combined to match ????
can you give more examples and expected results

Book3
	A	B	C
1	Product	subscription	price?
2	10 Gym	2 years	500
3	blockbuster	9 months	50
4	Olive Garden	n/a	10
5
6
7	Product	Price
8	2 year 10 gym membership	500
9	1 year blockbuster card	50
10	3 year 10 gym membership	100
11	Olive Garden gift card	10
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=VLOOKUP("*"&A2&"*",$A$8:$B$11,2,FALSE)

 

[DrewP]

try
=VLOOKUP("*"&A2&"*",$A$8:$B$11,2,FALSE)

BUT it will find the first one in the list - if you have this more then once

for example anything with 10 gym in the first wil be found

9 months does no match anything nor does N/A
so how will the A and B - be combined to match ????
can you give more examples and expected results

Book3
	A	B	C
1	Product	subscription	price?
2	10 Gym	2 years	500
3	blockbuster	9 months	50
4	Olive Garden	n/a	10
5
6
7	Product	Price
8	2 year 10 gym membership	500
9	1 year blockbuster card	50
10	3 year 10 gym membership	100
11	Olive Garden gift card	10
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=VLOOKUP("*"&A2&"*",$A$8:$B$11,2,FALSE)

Thank you! This is a start. Can the formula void "Olive Garden" since there is not a 2nd partial match?

try
=VLOOKUP("*"&A2&"*",$A$8:$B$11,2,FALSE)

BUT it will find the first one in the list - if you have this more then once

for example anything with 10 gym in the first wil be found

9 months does no match anything nor does N/A
so how will the A and B - be combined to match ????
can you give more examples and expected results

Book3
	A	B	C
1	Product	subscription	price?
2	10 Gym	2 years	500
3	blockbuster	9 months	50
4	Olive Garden	n/a	10
5
6
7	Product	Price
8	2 year 10 gym membership	500
9	1 year blockbuster card	50
10	3 year 10 gym membership	100
11	Olive Garden gift card	10
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=VLOOKUP("*"&A2&"*",$A$8:$B$11,2,FALSE)

Thank you! Here is a little more context. My company is working on budgets and our vendors data has been all over the place. I need to match the last price we paid for the product but our invoice is broken into 2 columns whereas their data set is combined. Id like to void/ignore any results that don't contain both, or parts of both criteria

PH_Sales_Pivot_View (32).xls
	A	B	C
1	Search criteria
2	Product	Support	Price?
3	xByte for 1 Virtual Machine	Production Support and Subscription
4	xByte PRO per service unit	Basic Support and Subscription
5
6
7	Data Source
8	Product	Last Price Paid
9	Upgrade: xByte 8 Enterprise for 1 Virtual Machine	$ 1,200.00
10	xByte Data Store - 1 TiB for 3 years	$ 999.00
11	Production Support/Subscription for xByte for 1 Virtual Machine for 3 years	$ 999.00
12	Technical account manager for xByte PRO	15000
Sheet3

 

[etaf]

our invoice is broken into 2 columns whereas their data set is combined.

looks like the information is not a combination of the 2 fields
fo example
xByte for 1 Virtual Machine
and
Production Support and Subscription
is not an exact match anywhere of the 2 in the single text
only 1 of the colums is with the text as shown in red
the blue and yellow do not match

Production Support/Subscription for xByte for 1 Virtual Machine for 3 years

so in the examples you showed - none will match

maybe i'm misunderstanding , how you want a match to work