When entering the following formula as an Array it works. But adding the same formula to Data Validation, it displays an error. The same common error when you enter a wrong formula in the List option. However, if I use a cell reference instead of a table reference (not range), it works either with the Array and the Data Validation. This formula works for both =OFFSET(rngStart,MATCH(tblAccount[Type],rngColumn,0)-1,1,COUNTIF(rngColumn,tblAccount[Type]),1) This formula works for the Array only: =OFFSET(rngStart,MATCH($C$3,rngColumn,0)-1,1,COUNTIF(rngColumn,$C$3),1) Thanks