# Index Match Second or Third value

#### simselk

##### Active Member
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

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

##### MrExcel MVP
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))

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

• ian0411

#### mikerickson

##### MrExcel MVP
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)

• RichK

#### simselk

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

regards Simon

##### MrExcel MVP

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

##### Active Member

Col A is text, col B is numbers

regards Simon

##### MrExcel MVP

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))

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

#### mikerickson

##### MrExcel MVP
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)

Last edited:

#### Dimdim

##### New Member
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))

##### MrExcel MVP
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?

Replies
1
Views
110
Replies
10
Views
224
Replies
2
Views
69
Replies
1
Views
213
Replies
13
Views
348