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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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)),"")
 
Upvote 0
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)
 
Upvote 0
thanks Guys
However, I am trying to avoid the use of Ctrl Sht Enter, as my calc needs to be dynamic ...

regards Simon
 
Upvote 0
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?
 
Upvote 0
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)),"")
 
Upvote 0
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:
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top