Index Match Second or Third value

simselk

Active Member
Joined
Mar 14, 2005
Messages
279
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
 

Some videos you may like

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,805
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

Active Member
Joined
Mar 14, 2005
Messages
279
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,805
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
Joined
Nov 27, 2014
Messages
3
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,539
Messages
5,548,631
Members
410,861
Latest member
Victor96
Top