Index Match Second or Third value

[simselk]

Hi All

My mind is blank on this ... I am trying to use Index Match to find the 2nd or 3rd value from a data array (ie A1:B6)
Col A contains names ie ABC, Col B has various numbers.
I've tried this formula: =INDEX(B1:B6,MATCH(A12,A1:A6,FALSE)+1,1)

However instead of referencing the second value for "ABC", it merely references the value of the cell (1 row) below the 1st value of "ABC"

any help much appreciated. thanks Simon

 

[Aladin Akyurek]

Hi All

My mind is blank on this ... I am trying to use Index Match to find the 2nd or 3rd value from a data array (ie A1:B6)
Col A contains names ie ABC, Col B has various numbers.
I've tried this formula: =INDEX(B1:B6,MATCH(A12,A1:A6,FALSE)+1,1)

However instead of referencing the second value for "ABC", it merely references the value of the cell (1 row) below the 1st value of "ABC"

any help much appreciated. thanks Simon

Control+shift+enter, not just enter...

=INDEX(B1:B6,SMALL(IF(A1:A6=A12,ROW(B1:B6)-ROW(B1)+1),2))

or, with a control added:

=IF(COUNTIF(A1:A6,A12)>=2,INDEX(B1:B6,SMALL(IF(A1:A6=A12,ROW(B1:B6)-ROW(B1)+1),2)),"")

 

[mikerickson]

You might try the CSE formula

=INDEX(B:B, SMALL(IF(A1:A6=A12,ROW(A1:A6)),2), 1)

This needs to be confirmed with Ctrl-Shift-Enter (Cmd+Return for Mac)

 

[simselk]

thanks Guys
However, I am trying to avoid the use of Ctrl Sht Enter, as my calc needs to be dynamic ...

regards Simon

 

[Aladin Akyurek]

thanks Guys
However, I am trying to avoid the use of Ctrl Sht Enter, as my calc needs to be dynamic ...

regards Simon

You can convert A1:B6 into a list/table on Excel 2003 version and beyond. This allows formulas to adjust to the changing range.

Othwerwise, you can create dynamic named ranges. Care to report the kind of data A1:A6 and B1:B6 house - text, numeric?

 

[simselk]

thanks Aladin

Col A is text, col B is numbers

regards Simon

 

[Aladin Akyurek]

thanks Aladin

Col A is text, col B is numbers

regards Simon

Let Sheet1 (adjust to suit) house the data in currently A1:B6.

Define Size (via Insert|Name|Define) as referring to:

=MATCH(9.99999999999999E+307,Sheet1!$B:$B)-ROW(Sheet1!$B$1)+1

Define Arange as referring to:

=OFFSET(Sheet1!$A$1,0,0,Size)

Define Brange as referring to:

=OFFSET(Sheet1!$B$1,0,0,Size)

Now invoke:

Control+shift+enter, not just enter...

=INDEX(Brange,SMALL(IF(Arange=A12,ROW(Brange)-ROW(INDEX(Brange),1,1)+1),2))

or, with a control added:

=IF(COUNTIF(Arange,A12)>=2,INDEX(Brange,SMALL(IF(Arange=A12,ROW(Brange)-ROW(INDEX(Brange),1,1)+1),2)),"")

 

[mikerickson]

If col A is sorted ascending this non-CSE formula that uses named ranges might work

=LOOKUP(A12, OFFSET(colA, COUNTIF(colA,A12)-2, 0), colB)

 

[Dimdim]

Is it possible to change this to account for an index/match scenario?

=INDEX(Breakdown[Title],MATCH("*"&[@Name]&"*",Breakdown[Title],0))

or simply

=INDEX(A1:A400,MATCH(C1,A1:A400,0))

 

[Aladin Akyurek]

Is it possible to change this to account for an index/match scenario?

=INDEX(Breakdown[Title],MATCH("*"&[@Name]&"*",Breakdown[Title],0))

or simply

=INDEX(A1:A400,MATCH(C1,A1:A400,0))

Didn't that work for you?