Help! Reference a cell in same column if Range meets criteria

**Trevorrow** · 02-12-2013, 02:47 PM

Hi Everyone,

I'm looking for a formula that will allow me to display a cell's value if the criteria for a range is met. This is a little more complex than a IF Formula.

Example:

I have range I9:Z9, I want to display the value of the cell in row 3 of the same column that meets my criteria. My Criteria is the first instance of the letter "A".

So if "A" shows up for the first time in cell T9, I would like the corresponding value in cell T3 to be displayed. "A" will show up several times in the range, I would just like the first instance. Will this need to be done through VBA? Is this possible?

Thanks,

Trevorrow

**ConneXionLost** · 02-12-2013, 03:14 PM

Try this:

=IF(COUNTIF(I9:Z9,"A"),INDEX(I3:Z3,1,MATCH("A",I9:Z9,0)),"")

**Special-K** · 02-12-2013, 03:18 PM

Try
=INDEX(I3:Z3,1,MATCH("A",I9:Z9))

**Trevorrow** · 02-12-2013, 03:31 PM

Originally Posted by Special-K

Try
=INDEX(I3:Z3,1,MATCH("A",I9:Z9))

This works! However, it is returning the last instance of "A" instead of the first. I may be able to work with this, but is there an easy fix?

**Trevorrow** · 02-12-2013, 03:27 PM

I should mention the range I am defining and cell value I am looking to display are on another sheet, let's call it "Sheet 1". I'll be more specific with ranges:

First instance of the letter "A" in range 'Sheet1'!$I9:$BDM9
Then display value in Row 3 of same column of same sheet.