Adding 1 criteria into existing formula

Hi guys, would you know what we could add into existing formula:

=IFERROR(VLOOKUP(K13,Countries!$C$11:$I$13,6,0),"")

...to say 'Do as per formula but ONLY if 'No' is also selected in Countries!$A$11:$A$13 ?

The suggested is a working formula, so no creating new is needed, its just about adding that one additional criteria!

Attaching a file and a screenshot. Thanking in advance!

https://www.excelforum.com/attachmen...1&d=1530387312 [EXCEL]

2018-06-30_2128.png

Thanks for looking into it, protonLeah.

It's A11:A13 range, not just a single cell

Hi,

If All 3 cells Countries!$A$11:$A$13 needs to be "No", then:

=IF(COUNTIF(Countries!$A$11:$A$13,"No")=3,IFERROR(VLOOKUP(K13,Countries!$C$11:$I$13,6,0),""),"")

If Any of the 3 cells is "No", then:

=IF(COUNTIF(Countries!$A$11:$A$13,"No"),IFERROR(VLOOKUP(K13,Countries!$C$11:$I$13,6,0),""),"")

Thank you guys for your valuable suggestions. And sorry for late reply.

I tried implementing your formulas, but here is a challenge:


Basically, if a country is marked as 'Past' then its already a visited country. If a country is marked as 'No' then its the one to be visited in a future.

Problem occurs, however, if we have same country which was already visited (Past) AND same country in a list is entered twice, for another visit in a future years.

The formula:

=IFERROR(VLOOKUP(K13,Countries!$C$11:$I$13,6,0),""))

...is to tell us a year at which a country is to be visited. But a formula gets confused IF same country is entered twice. So to filter the confusion, formula should look only for a year to a country which is marked as 'No', ignoring entries of a same country if it is marked as 'Past'

If a country is marked as 'Yes' then it means that it was recently visited and formula should behave in same way as if a country would be marked as 'No', (i.e. show a year entered next to a country, while ignoring any entries of same country marked as 'Past')

Difficult to explain otherwise, but hoping I was able to!

Thanks once again.