Modify a IF/VLOOKUP to account for 3 more variables

[slam]

I need to modify a formula to account for 3 more variables, and am having a lot of trouble. I have reverted to my original formula as shown below:

=IF(ISNA(VLOOKUP($B3,Australia!$B$2:$H$17,7,0)),"",VLOOKUP($B3,Australia!$B$2:$H$17,7,0))

What I need to additionally do is:
1. Display DNS as the result if the value from from B3 is not in the range Australia!B2:B17

2. Display DNF as the result if the value from B3 has a corresponding matching value from Australia!B2:B17, and if the corresponding row in Australia!F2:F17 = DNF and if Australia!H2:H17 = 0

3. Display DSQ as the result if the value from B3 has a corresponding matching value from Australia!B2:B17, and if the corresponding row in Australia!F2:F17 = DSQ

I greatly appreciate whatever anyone can help with. Thanks

 

[Andrew Poulsom]

Try:

=IF(COUNTIF(Australia!$B$2:$B$17,$B3),IF(AND(VLOOKUP($B3,Australia!$B$2:$H$17,5,0)="DNF",VLOOKUP($B3,Australia!$B$2:$H$17,7,0)=0),"DNF",IF(VLOOKUP($B3,Australia!$B$2:$H$17,5,0)="DSQ","DSQ",VLOOKUP($B3,Australia!$B$2:$H$17,7,0))),"DNS")

 

[slam]

Wow, that works perfectly! That was a very fast reply. Thanks so much!