Creating a MAX IF or LARGE IF wildcard formula

**leesrubberroom** · 02-19-2013, 06:58 AM

Hi everyone, i'm new to this forum, but i'm not new to Excel, so here's my first post. I want to create a formula for MAX IF or LARGE IF type formula, that's also a wildcard search. I can do the IF wildcard formula, and i can do a MAX IF or LARGE IF using an array, but i can't seem to get the 2 combined. Basically, i have Column A full of text data which are part numbers, and a lot of the time an individual cell contains multiple part numbers. in column B, i have numeric values, and i want to find the largest value in column L from where the cells in column C contain a certain set of text.

So in the attached example, the formula with a search for A or C would yeild 240, for B would be 72, and D would be 100.
To make matters more complicated, i want to enter the search value in cells, not hard coded in to the formula, and i dont want to manipulate the original dataset, as this is produced externally, so i'd have to do this everytime.

Hope someone can help me.

**Ace_XL** · 02-19-2013, 07:14 AM

Try this array formula

Please Login or Register  to view this content.

where G1 holds the value that you are searching viz. A,B,C,D

Confirm with Ctrl+Shift+Enter

**twiggywales** · 02-19-2013, 07:16 AM

try this

Please Login or Register  to view this content.

F2 is the cell you put your search in to

this is array entered (Control+Shift+Enter)

darn beaten to it again

**:) Sixthsense :)** · 02-19-2013, 07:19 AM

Formula:

Please Login or Register  to view this content.

**samba_ravi** · 02-19-2013, 07:27 AM

Please see the attached file

**leesrubberroom** · 02-19-2013, 07:42 AM

Perfect. That's just the ticket.
I just need to get my head around exactly how that works to i can get do it myself next time!

Thanks very much.

**twiggywales** · 02-19-2013, 08:13 AM

=SEARCH(F2,A1:A4) creates an array of numbers (where they are in the cell) or errors where there is no match.

=ISNUMBER(Above) checks to see if its a number and creates an array of TRUE / FALSE values

this can then be used either with the if or the way 6th sense used it which then creates an array of blanks or number results.

max then picks out the highest value.

see attached book for a clearer explanation

Book1 (1).xlsx