Locate the seocnd furthers left value in a horizontal range

[Mitch Connor]

Hi,

I posted a thread before here

http://www.excelforum.com/excel-form...tal-range.html

Building on this question, I would be grateful for guidance on selecting the second furthest left of a certain value which appears multiple times in a horizontal range and then pulling through the value immediately to its left. For instance I would be looking to pull through 56, 3 and 34 from 3 rows working down where B is the value concerned.

[daddylonglegs]

You can use this "array formula" in M6

=INDEX(A6:K6,SMALL(IF(B6:L6="B",COLUMN(B6:L6)-MIN(COLUMN(B6:L6))+1),2))

confirmed with CTRL+SHIFT+ENTER and copied down

The 2 at the end of the formula gives you the 2nd value from the left, if you change to 3, for example, you will get the 3rd match from the left. If there isn't an nth match then you'll get an error, use IFERROR function around the above formula to return another value if you require

[Tony Valko]

Try this array formula**:

=INDEX(A6:K6,LARGE(IF(B6:L6="B",COLUMN(B6:L6)-COLUMN(B6)+1),2))

** 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.

[Mitch Connor]

Cheers guys, they work a treat and the fast responses are appreciated.

[Tony Valko]

I misunderstood what you wanted but the formula works due to dumb luck!

I thought you wanted the 2nd instance from right to left. In the sample file there were always 3 instances of the lookup value so looking from left to right or right to left would always give the same correct results.