Avoiding Circular Reference

[ffffloyd]

Here's a tricky one.

Let's say I have a sheet called Category. Column A contains code; column B contains description.

Now I have another sheet in which either column F contains a category code or column G contains a category description. I want to look up the code in column F if the description is given or the description in column G if the code is given.

I thought I might be able to do something like:

in F2: IF($G2<>"",INDEX(Category!A:A,MATCH($G2,Category!$B:$B,0)),"")
in G2: IF($F2<>"",INDEX(Category!B:B,MATCH($F2,Category!$A:$A,0)),"")

(and then copy the formulae down the rest of the columns of course)

In other words, if the cells were both empty then there would be no common dependency; it would just set them both to empty. However, if I provided either value then it would overwrite the formula in that cell so there would be only one formula left and nothing to be circular dependent about.

However, it must be looking at the condition itself and seeing the two columns in common because it is coming up with a circular dependency. I thought I might be able to trick it by having IF(INDEX($G:$G,ROW())<>"",... but it still doesn't work.

This sort of situation must crop up from time to time. How do we get around it?