Vlookup and multiple ifs with text values

[concretejungle16]

All,

I have 2 tabs.

Tab 1 contains a column where I want the returned results to display, titled "Statuses."

Tab 2 contains a column that could have just one of a numeric value (one number 5), or multiple (three number 8s).

I want to lookup all instances of a number and associated TEXT values in the same row. Those three number 8s could have Untested, Currently Testing, Passed, or Failed listed.

Finally, I want to return the result:
- If any say Failed, returned result in Tab 1 is Failed
- If any say Untested, returned result in Tab 1 is Untested
- If all say Passed, returned result in Tab 1 is Passed
- If not all say Passed but none say Failed, returned result in Tab 1 is Currently Testing


I'm absolutely stumped on what this formula should look like. Help

 

[steve the fish]

Try this. Your value to test in A1. You columns of values in Sheet2!A:B.

=IF(COUNTIFS(Sheet2!A:A,A1,Sheet2!B:B,"Failed"),"Failed",IF(COUNTIFS(Sheet2!A:A,A1,Sheet2!B:B,"Untested"),"Untested",IF(COUNTIFS(Sheet2!A:A,A1,Sheet2!B:B,"Passed")/COUNTIFS(Sheet2!A:A,A1)=1,"Passed","Currently Testing")))

 

[concretejungle16]

It worked! I've been working at this for 2 days. You are a lifesaver!