Originally Posted by
Babydum
OK, I have one last question:
where do I put an error trap in this formula:
=IF(D1="OVR",E1,IF(OR(ISBLANK(A1),ISBLANK(B1)),"Please Add This Yourself",INDEX(Sheet2!C$2:C$13,MATCH(A1&B1,Sheet2!A$2:A$13&Sheet2!B$2:B$13 ,0))))
In other words, I want it so that if a match is not found it returns the words "No Match Found" in the cell.
I've managed to do it with the formula:
{=IF(ISERROR(INDEX(Sheet2!C$2:C$13,MATCH(A1&B1,Sheet2!A$2:A$13&Sheet2!B$2:B $13,0))),"No Match Found",IF(D1="OVR",E1,IF(OR(ISBLANK(A1),ISBLANK(B1)),"Please Add This Yourself",INDEX(Sheet2!C$2:C$13,MATCH(A1&B1,Sheet2!A$2:A$13&Sheet2!B$2:B$13 ,0)))))}
But that is one UGLY formula. Surely there has to be an easier way of handling this... I mean, come on!!
Always one last question!
First, you don't need to test all of this
ISERROR(INDEX(Sheet2!C$2:C$13,MATCH(A1&B1,Sheet2!A$2:A$13&Sheet2!B$2:B$13,0)))
as the error will be in the Match so the first cut can be
=IF(ISERROR(MATCH(A1&B1,Sheet2!A$2:A$13&Sheet2!B$2:B$13,0)),"No Match Found",IF(D1="OVR",E1,IF(OR(ISBLANK(A1),ISBLANK(B1)),"Please Add This Yourself",INDEX(Sheet2!C$2:C$13,MATCH(A1&B1,Sheet2!A$2:A$13&Sheet2!B$2:B$13,0)))))
Then you can further improve it by using names. Put the cursor in the cell with the formula and define a name of say myMatch for
MATCH(A1&B1,Sheet2!A$2:A$13&Sheet2!B$2:B$13,0)
. To do this, goto menu
Insert>Name>Define..., put myMatych in the names box, and
=MATCH(A1&B1,Sheet2!A$2:A$13&Sheet2!B$2:B$13,0)
in the Refersto box. The formula can then be reduced to
=IF(ISERROR(myMatch),"No Match Found",IF(D1="OVR",E1,IF(OR(ISBLANK(A1),ISBLANK(B1)),"Please Add This Yourself",INDEX(Sheet2!C$2:C$13,myMatch))))
Your company should employ me as a c onsultant