Index Match Formular based on multiple Criterias if cell contains a Value I have

Question

Index Match Formular based on multiple Criterias if cell contains a Value

I have problem that i am tryying to get my head arround. What i am looking findout is the maximum "issue date" by area and drawings that contain the Title "Vendor" and "without Vendor". The titles "of drawings change and are not always consistant so i think it needs to be based on =isnumber(search("Without" or "vendor" ? but i dont know how i can build this into a formular. I have tried indexing buut with no luck, i caan only get this to return the max value when using Index(match, but this doesnt take note of the area or Drawing title.

Thanks for you help

A1 C1 D1 A2 Area Drawings Issue Date A3 41 Drawings Without Vendor 01/01/2012 A4 41 Drawings With Vendor 02/01/2012 A5 41 Without Vendor 03/01/2012 A6 41 Drawings With Vendor 04/01/2012 A7 42 Drawings Without Vendor 05/01/2012 A8 43 Area 42 Drawings With Vendor 06/01/2012

Explanation / Answer

Hi,

Try this array formula with the lookup value in E1

See below for how to enter an array formula.

=MAX(IF(A1:A100=E1,IF(OR(ISNUMBER(SEARCH("Vendor",B1:B100)),ISNUMBER(SEARCH("Without",B1:B100))),C1:C100))))

This is an array formula which must be entered bby pressing CTRL+Shift+Enter
and not just Enteer. If you do it correctly then Exxcel will put curly brackets
around tthe formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---