Function that will bring in text if it exists in one of every other cell on the same row.

[pglufkin]

I need a function that will bring in text if it exists in one of every other cell on the same row to the end of the worksheet.

So for instance, the function will reside in cell A1. If text exists in C1 or E1 or G1 . . . . in that series, all the way to the end of the worksheet, I need that text. Assume text only exists in one of the cells of interest (in only one of the odd columned cells on that row).

I will have text in the even columned cells (B1, D1, F1 . . . ) that I will want to ignore.

I also need as a separate function the cell address where that text arrived at above is found.

Thanks much!

[Ace_XL]

Try this

To return text
=INDEX(C1:FM1,MATCH(1,INDEX((ISTEXT(C1:FM1))*(MOD(COLUMN(C1:FM1),2)=MOD(COLUMN(C1),2)),0),0))

Address of cell
=ADDRESS(ROW(),MATCH(1,INDEX((ISTEXT(C1:FM1))*(MOD(COLUMN(C1:FM1),2)=MOD(COLUMN(C1),2)),0),0)+2)

[pglufkin]

awesome, thank you very much . . . works like a charm!

[pglufkin]

Except one thing, the cell address function is not relative . . . so if i get a 'hit' 2 columns away from the function, the cell address is column "C" even though my function is input in column F. If my cell address function is input in column F and my 'hit' is two columns away, the cell address should reference column H . . . but it doesn't, it references column C.

[Ace_XL]

the +2 at the end of the formula takes into account the fact that your range (C1:FM1)starts from Column C

Adjust accordingly
i.e.
for D:FM use +3
for E:FM use +4
and so on

[pglufkin]

I see, I got it, thank you.

[pglufkin]

Ok this works very well and I thank you. These functions render the 'first hit' of text in the row of possible texts. As a variation now, I would like a function or UDF that toggles the next bit of text that exists that meets the criteria in the sequence first described above.