Hello everyone,

I'm a bit stuck on a formula. I have a large data set with thousands of rows of data and somewhere around 40 columns. I need a formula to locate any instance of a value with that array and return the cell address of EVERY instance of the value. The formula I finally worked out only gives me one (the lowest) instance of the found value. However, I haven't been able to figure out how to expand it to give me multiple addresses if the value is found more than once. Also, I haven't added error checking yet, so if the value is not found, the formula just returns $Z$100.

=ADDRESS(MIN(IF($A$2:$F$12=$I8,ROW($A$2:$F$12),100)),MIN(IF($A$2:$F$12=$I8,COLUMN($A$2:$F$12),26)))

If anyone can help me expand it to allow me to find all instances, I would seriously appreciate it. Also, if anyone can figure out the error checking WITH the multi-address return, that'd also be great.

NOTE: the ranges I have in the formula above is just my testing range.