Using Vlookup in Excel sheet to match substring

Question

Please find my Question below, to show the tables I have made the question into a image:

For non matching text I want to output value as 0.

Answer 1

You can find the relevan data with an array formula (Ctrl+Shift+Enter) like this:

{=MIN(IF(ISERROR(FIND(Sheet1!$A$3:$A$5,A3)),FALSE,Sheet1!$B$3:$B$5))}

(This assumes your pricing descriptions are in Sheet1 at A3:A5, and your product titles ar at A3 and downwards on the other sheet)

You can then copy (drag-copy or otherwise) as far as you need. Do note that if you have strings that match multiple rows, you'll get the minimum cost (You could change to a different aggregate functions if you need that).

The way this formula works is:

1. FIND(Sheet1!$A$3:$A$5,A3): Returns an array of indices or error values, depending on matching strings.
2. IF(ISERROR(FIND..): Tests each array item for error and ->
3. For error values (no match) return FALSE.
4. For non-error values (match) return requested value, according to array position / index (Sheet1!$B$3:$B$5).
5. MIN(....): Return the minimum value of an array consisting of some FALSE values and some (hopefully just 1) numbers. This aggregates the array into a single number.

Answer 2

Since you can't match identical word from Sheet1 Pricing to Sheet2 Product you need to insert new column between colum Description and Cost in Sheet1 Pricing. In this column insert formula:

=VLOOKUP("*"&MID(A2,2,9)&"*",Product!$A$2:$A$4,1,0)

Use MID function because, as far as I can see, you dont have identical part of frase in sheet2 Product (eg "Pack of 3 HD" - "Premium pack of 3 Matte Sreen")

Now you can use VLOOKUP formula in sheet2 Product using new column for reference:

=IFERROR(VLOOKUP(A2,Pricing!$B$2:$C$4,2,0),0)