Need to update formula to a third value


I need to add a third value so that it now reads Tags, Test Team, Test Type. What is the best way to do this? Below are the two formulas present on the worksheet.


C2 Formula

=IFERROR(VLOOKUP(MIN('Bot Reference'!$C:$C)+(COLUMN()-COLUMN($C$2)),'Bot Reference'!$C:$D,2,FALSE),"$MD.Tags")


D2 Formula

=IF(OR(C2="$MD.Test Team",C2=""),"",IFERROR(IF(C2="$MD.Tags","$MD.Test Team",IFERROR(VLOOKUP(MIN('Bot Reference'!$C:$C)+(COLUMN()-COLUMN($C$2)),'Bot Reference'!$C:$D,2,FALSE),"$MD.Tags")),""))

1 Reply



I'll offer a general principle in response to your question. That general principle--and you're experiencing the "Why?" behind it--is to avoid "hard-coding" variables in your formulas. 


What to do instead? One alternative--often suitable--is to use lookup tables, which you are already doing in some way, evident since you are using VLOOKUP. That makes me think that maybe a multiple dimensional table or a method to choose different tables, might work for you.


I'm attaching an example where I did this latter--to allow for different tables to be used within a single formula (to calculate the US income tax) changing the underlying table based on filing status. It's not directly applicable, but perhaps the principle in the design is.