Index Match Formular based on multiple Criterias if ccell contains a Value I hav

Question

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

I have problem that i am trying to get my head arround. What i am lookking findout is the maximum "issue date" by area and drawings that contain the Title "Vendor" and "without Vendor". The titles of drrawings 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 but with no luck, i can onlyy 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 muust bee entered by preessing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brrackets
around the 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

---