# Thread: How to index match MAX or MIN value of a partial text in a table

1. ## How to index match MAX or MIN value of a partial text in a table

Hi all,

I am trying to find price ladders Min and Max prices from a list of products which contain the text in a cell using the Index match formula.
Not sure it is the best formula for the use but basically, I have a list of product name in column A, and in a separate table I have a column with products which contain many variations of this main product with various prices which are in another column.

I would like to have the Min value in the price column of all the products containing the text in column A.

Ex: in table 1, A1= Belinda

In my second table:
A1:A200=
Belinda Red
Belinda Steel
Belinda Short
Arnold basic
Arnold Gold
etc...

Tab2, B1:B200=
10\$
20\$
12\$
3\$
5\$
etc...

I would like to find Min value for anything that contains Belinda which would be: 10\$
And max value: 25\$.

I have tried few variations but can not find the right value.

2. Can you post a sample of your worksheet?

3. Thank you for your quick reply

Here is the example on the screenshot and the actual file is attached:

From the "after" tab I d like to search for anything that contains "belinda" in the "complete list tab" column E and report the MIN price value (tab "complete list" column H of all product descriptions containing the text in column B of the tab "after" if that make sense.

Table 1.jpgTable 2.jpg

4. Umit,

You can use a User Defined Function (UDF) to do this. Place the following code in a standard module.

In a Standard Module:
Code:
```Public Function LowPrice(rng As Range) As Double
Dim cell As Range, cost(), index As Integer
index = 1
With Worksheets("complete pricelist")
For Each cell In Range("Description")
If InStr(1, cell, rng, vbTextCompare) > 0 Then
ReDim Preserve cost(index)
cost(index) = .Cells(cell.Row, "H")
index = index + 1
End If
Next cell
If index = 1 Then
LowPrice = 0
Else:
LowPrice = WorksheetFunction.Min(cost())
End If
End With
End Function

Public Function HighPrice(rng As Range) As Double
Dim cell As Range, cost(), index As Integer
index = 1
With Worksheets("complete pricelist")
For Each cell In Range("Description")
If InStr(1, cell, rng, vbTextCompare) > 0 Then
ReDim Preserve cost(index)
cost(index) = .Cells(cell.Row, "H")
index = index + 1
End If
Next cell
If index = 1 Then
HighPrice = 0
Else:
HighPrice = WorksheetFunction.Max(cost())
End If
End With
End Function```
On the After sheet in cell E4, place the following formula: =LowPrice(B4) then copy down

On the After sheet in cell F4, place the following formula: =HighPrice(B4) then copy down

The UDF will look for the value in cell B4 within each cell the Description column E on the complete pricelist sheet. If matches are found then the code assigns the corresponding prices in col H to an array variable to which it obtains the min and max values. Of no matches are found, it returns 0 for the min and max values ("-" with the accounting format you have applied)

HTH,
Maud

umit1.png

5. ## The Following User Says Thank You to Maudibe For This Useful Post:

Umit (2015-08-08)

6. Maud,

Thank you it works on the sample but I just have an issue when I double click in the product name cell, it generates an error:

name error.jpg

And when I copied the code in the original spreadsheet it doesn't work neither. I must admit I have never used UDF or vba before, not sure If I have done something wrong. Here are the screenshots of the VBA and the original xls file error:

VBA.jpg

I can not attach the original file as it is too heavy for the forum but I can send it be email if that's ok for you (2Mo).

Many thanks

7. Umit,

My bad! I forgot to mention that I created a named range called Description which is on the complete pricelist sheet E4:E6365. The code uses Description to check for a matching value. Please create that named range. Everything you did looks right.

Maud

Umit2.png

8. ## The Following User Says Thank You to Maudibe For This Useful Post:

Umit (2015-08-08)

9. Maud, It works perfectly!

Thank you soo much!

Umit

10. ## Similar function with multiple text sources + NB and SUM

Hi Maud,

Your last post has been really helpful.

I do not want to be asking too much but I think you can help me with this one which is slightly more complicated and again I can not find standard excel functions to solve it but it requires a similar method

What I need to do is:

On the example below,

1) In the column J, I would like Excel to count the number of cells which contain both the value in the column B and the one in the cell I3 in the named range called Description.

2) In the column H, I would like to have the sum of the prices of cells which contain both the value in the column B and the one in the cell I3 in the named range called Description.

Example.png

I think it is 2 different functions.

11. Not a problem at all. Could you please clarify which sheet(s) each of the columns and cell I3 refer to?

Thanks,
Maud

12. ## The Following User Says Thank You to Maudibe For This Useful Post:

Umit (2015-08-11)

13. Hi Maud,

Apologies my bad, I meant G3.

Sheet "after" Cell G4: I would like to have the sum of the column H of sheet "Complete pricelist" for all the cells in column E of the sheet "complete pricellist" which containt both the text in cell B4 and G3 of the sheet "after".

I previously created the named range "Description" for the column E of the sheet "complete pricelist" for the previous function.

In the cell H4 of the sheet "after" I would like to count the number of cells in the column E of the sheet "complete pricelist" which containt both the text in G3 and B4 of the sheet "after" so that I can eventually have the average cost in I4 (sheet "after") by dividing the value in G4 by the number of cells in H4.

Then I would like to copy and paste this formula for all the other columns (J,K,M,N) in sheet "after".

I have tried to do it with NB.IF and SUM.IF functions but I would have to manually write both text in each cell and I have over 1000 cells by sheet and 10 sheets . Example below:

text match for sum and nb.jpgtext match for sum and nb2.jpg

I hope it makes sense.

14. Umit,

In cell G4 place the formula =FMethod(B4) and copy down
In cell H4 place the formula =Sku(B4) and copy down

Function FMethod will sum all the cells in the H column (Complete pricelist sheet) where:
1. The parent name value and the word "base plate" from the After sheet are both found in the corresponding values in the Description Column

Function Sku will do the same but only count them

Both methods are versatile so they can be used in the repeating pairs of columns. Keep in mind that there are more efficient methods to achieve the same results.

HTH,
Maud

Code:
```Public Function FMethod(rng As Range) As Currency
Dim cell As Range, cost As Currency, col As Integer
col = Application.Caller.Column
cost = 0
With Worksheets("complete pricelist")
For Each cell In Range("Description")
If InStr(1, cell, rng, vbTextCompare) > 0 And _
InStr(1, cell, Cells(3, col), vbTextCompare) > 0 Then
cost = cost + .Cells(cell.Row, "H")
End If
Next cell
BasePlate = cost
End With
End Function

Public Function Sku(rng As Range) As Long
Dim cell As Range, count As Long, index As Integer
col = Application.Caller.Column
count = 0
With Worksheets("complete pricelist")
For Each cell In Range("Description")
If InStr(1, cell, rng, vbTextCompare) > 0 And _
InStr(1, cell, Cells(3, col), vbTextCompare) > 0 Then
count = count + 1
End If
Next cell
Sku = count
End With
End Function```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•