ISNA with Index,Match formula - need to add on

[n8dine]

I have the following formula and need to add to it:

=IF(A2="","",IF(ISNA(IF(BA2="1. Phase","Project",INDEX(Sheet1!O:O,MATCH(TEXT(D2,"00000")&'2-Details'!BZ2,Sheet1!D:D,0)))),0,IF(BA2="1. Phase","Project",INDEX(Sheet1!O:O,MATCH(TEXT(D2,"00000")&'2-Details'!BZ2,Sheet1!D:D,0)))))

After the first test where if A2="","", I need to specify that if G2=Lists H1 or H2 then return Project, otherwise continue with above.

Any ideas for writing this in Excel 2003 would be greatly appreciated.

Nadine

 

[T. Valko]

I have the following formula and need to add to it:

=IF(A2="","",IF(ISNA(IF(BA2="1. Phase","Project",INDEX(Sheet1!O:O,MATCH(TEXT(D2,"00000")&'2-Details'!BZ2,Sheet1!D:D,0)))),0,IF(BA2="1. Phase","Project",INDEX(Sheet1!O:O,MATCH(TEXT(D2,"00000")&'2-Details'!BZ2,Sheet1!D:D,0)))))

After the first test where if A2="","", I need to specify that if G2=Lists H1 or H2 then return Project, otherwise continue with above.

Any ideas for writing this in Excel 2003 would be greatly appreciated.

Nadine

Hmmm...

Why is this expression inside the ISNA function:

IF(BA2="1. Phase","Project"

If there's a #N/A error it will come from the MATCH fucntion.

What is the data type of the result of the INDEX/MATCH? Is it a TEXT value?

 

[n8dine]

The Index, Match result is a text value if there is a match.

 

[T. Valko]

The Index, Match result is a text value if there is a match.

Off the top of my head...

=IF(A2="","",IF(OR(G2=Lists!H1,G2=Lists!H2,BA2="1. Phase"),"Project",LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(Sheet1!O:O,MATCH(TEXT(D2,"00000")&'2-Details'!BZ2,Sheet1!D:D,0))))))

 

[n8dine]

Thank you for trying. Unfortunately, the formula is not working where it looks to Lists H1 or H2. When I manually look up the value in G I find it on the Lists worksheet but the formula is returning #N/A. I also get #N/A in those instances where there is no match to Sheet1.

 

[T. Valko]

Thank you for trying. Unfortunately, the formula is not working where it looks to Lists H1 or H2. When I manually look up the value in G I find it on the Lists worksheet but the formula is returning #N/A. I also get #N/A in those instances where there is no match to Sheet1.

If you're getting #N/A errors there must be #N/A errors in some of the cells being referenced.

I have no idea what's in any of your cells but in my test file I the formula works (no #N/A errors).

 

[n8dine]

The problem with some of the #N/A's are that I can manually find the matches that the formula cannot find. I guess I'm just out of luck with adjusting the formula since it doesn't work with my data. Thanks for trying, though. I truly appreciate the effort.