Filter matrix based on a non-exact match

**douglascaixeta** · 03-31-2013, 12:04 AM

I would like to make an average of the numbers in column B that matches a criteria on column A. The problem is that is not an exact match.

The names below are on column A:

VW Gol 10/11 - AADDD
FIAT Strada 09/10 - HHH3456
FORD Fox 08/09 - ZZZ3333
VW Gol 13/13 - FRT4444
FORD Fox 02/02 - RRR4444

Column B contains numbers.

I want an average of numbers that match VW Gol and FORD Fox etc. All of them. Which means excel need to ignore what comes later and gather all the numbers and make an average. The problem is that what comes later is not in a defined number of characters. Is it possible?

**dredwolf** · 03-31-2013, 12:41 AM

You could try using something like SEARCH("VW Gol*",A1) (or find) or something like that ...

EDIT-
no specific formula, because, no specific data

**FDibbins** · 03-31-2013, 12:42 AM

Hi

assuming your data is in A1:B5 - I put VW Gol in A10 and Ford Fox in A11. Then in B10, I used this, and copied it to B11...

=AVERAGEIF($A$1:$A$5,A10&"*",$B$1:$B$5)

Adjust ranges as needed

**rcm** · 03-31-2013, 12:45 AM

try this macro, type in the desired partial match in A1 and run it

Please Login or Register  to view this content.

**douglascaixeta** · 03-31-2013, 03:52 PM

@dredwolf : I don't know what to do with this. It will give me back the position of the text in the field. Doesnt really help.

@FDibbins: unfortunately no. The actual data is more than a thousand records.

@rcm: I don't know how to run this.

There are no formula that will give back all the cells that contains the given text?

**FDibbins** · 03-31-2013, 03:57 PM

The qty of records is irrelevant, just extend the range to cover as needed.

=AVERAGEIF($A$1:$A$1000,A10&"*",$B$1:$B$1000)

There are no formula that will give back all the cells that contains the given text?

Thats what A10&"*" does

**dredwolf** · 03-31-2013, 04:02 PM

the search option would be used within a larger formula, which I did not give because I had nothing to work with, But FDibbins solution seems to cover it so no need to use the search optiion

**douglascaixeta** · 03-31-2013, 04:32 PM

Yes FDibbins, that works perfectly. I found that solution just know. I understood that you meant to filter the data and set different ranges per formula.

Thanks!