Need the correct formula for an IF statement...

[jeptik]

Hi All,

Need help on writing a formula that has me stumped.

Below is an image of the excel document.

Here is the formula that I am currently using that works for the Threshold Vacancy but as I mention below, it needs to incorporate both logic in the one formula.

=IF(D2<B1,"YES","NO")

I need my formula to point to the City Vacancy if I decide to not use the threshold vacancy. So if threshold vacancy is blank, then point to City Vacancy and tell me if the threshold is met (YES/NO). So I need a formula to incorporate both B1 & B2.

So, for more clarity on the logic....

If B1 is populated then return YES or NO if D2 is less than B1 and if B1 is blank then look at B2 and tell me if D2 is less than B2 and return a YES or NO.

Hope I am making sense in the logic of what I am trying to achieve.

Thanks for the help as I have been stuck on this.

Regards.

[jason.b75]

Thread title updated, change request removed.

[jeptik]

Thank you for pointing that out to me Jason.

jeptik

[jason.b75]

Thanks for editing your title!

See if this works.

=IF(D1< LOOKUP(1,B1:B2),"YES","NO")

[jeptik]

Hi jason.b75,

Thank you very much for your suggestion!

It works like a charm.

Now if I decide to add another row under City Vacancy with another value for Region Vacancy, would I just have to extend my formula to include B3 in this case?

Thanks.

[jeptik]

Hi jason.b75,

Thank you very much for your suggestion!

It works like a charm.

Now if I decide to add another row under City Vacancy with another value for Region Vacancy, would I just have to extend my formula to include B3 in this case?

Thanks.

[jason.b75]

It depends on order of priority, you could do it that way, for example

=IF(D1< LOOKUP(1,B1:B3),"YES","NO")

using this method will always take the last value in the list, so if all 3 cells have a %age figure, B3 will be used and the others ignored.

[jeptik]

Hmmm...

Now you have me thinking....is there a way to prioritize which % it looks at first? So, can I tell the formula to look at B1 first and if not available, then B2 and if not available then B3?

Thanks.

[Phuocam]

Try this ...

=IF(D1< INDEX($B$2:$B$6,MATCH(TRUE,$B$2:$B$6<>"",0)),"YES","NO")

Array formula, enter with Ctrl+Shift+Enter.

[jeptik]

Thanks Phuocam!!

Worked perfectly!

[Phuocam]

You are welcome!