Finding first character in a row of characters

**barneyrubble1965** · 03-19-2013, 03:45 AM

I have a large spreadsheet and within each row there are the letters "L" and "H". I want to type a formula which will give me the cell reference (e.g. F2) of the first occurence of the letter "H" in each row and this reference to be listed in column "B" for each row.

**Wormsek** · 03-19-2013, 03:51 AM

Try like this:

Please Login or Register  to view this content.

**barneyrubble1965** · 03-26-2013, 10:25 AM

Thanks for your suggestion, it is close to what I'm looking for but not quite. In attached excel spreadsheet I want to return the figure in row which corresponds to the first "H" appearing in each subsequent row and for this number to be in column "D" of each row

**Ace_XL** · 03-26-2013, 10:31 AM

Try

=IFERROR(INDEX($F$3:$DS$3,MATCH("H",$F4:$DS4,0)),"")

Copy down

**Tony Valko** · 03-26-2013, 10:38 AM

I didn't look at your sample file but maybe something like this will do...

Assume the range of interest is C2:J2 with the formula being entered in B2.

This array formula** entered in B2 and copied down as needed:

=ADDRESS(ROW(),MIN(IF(C2:J2="H",COLUMN(C2:J2))),4)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.