Need an excel formula.
The crude solution is:
Where ?City? appears when the city referenced in H2 is not part of the lookup
and ?State? appears when something other than WA appears in I2.
But there is probably a better way to do this using an array of valid values like the one below:
In the scenario you could stuff the array in another worksheet and use the HLookup function to find the "Sheet3!I2" value in the first row of this array to determine which column to look in, then VLookup "Sheet3!H2" in the column of that array to see if the city referenced exists. Of course this is a much more complex formula, but it would be easily extendible without changing the formula every time.
For mor info, see "Lookup and Reference Functions" in the Excel Help.
Jun 11, 2009 |
Microsoft Excel for PC