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

 

[dorlesky]

I am having a similar problem.

Column B has the search field. Column C has the values to return. Cell E9 contains the search value.

To return the 2nd, then 3rd, then 4th value, the array formula would have to adjust depending on the last item found. I have tried to incorporate a Match() into it but could not get it to work. And also when I tried to insert a row above Row 1 all the formulas are in error then. It seems it should be a simple solution, but for the life of me I can't get it to work.

A	B	C	D	E	F	G
	2000	Apple
	1500	Monkey
	1750	Elephant
	2000	Dog
	2100	Orange
	2200	Banana
	2000	Bat
	2000	Bob
				2000	Wrong Answers	Correct Answers
					Apple	Apple	{=IF(E9<>"",INDEX($C$1:$C$8,SMALL(IF($B$1:$B$8=E9,ROW($C$1:$C$8)),COUNTIF(B$1:B3,E9))),"")}
					Dog	Dog	{=IF(E9<>"",INDEX($C$1:$C$8,SMALL(IF($B$1:$B$8=E9,ROW($C$1:$C$8)),COUNTIF(B$1:B4,E9))),"")}
					Dog	Bat	{=IF(E9<>"",INDEX($C$1:$C$8,SMALL(IF($B$1:$B$8=E9,ROW($C$1:$C$8)),COUNTIF(B$1:B5,E9))),"")}
					Bob	Bob	{=IF(E9<>"",INDEX($C$1:$C$8,SMALL(IF($B$1:$B$8=E9,ROW($C$1:$C$8)),COUNTIF(B$1:B8,E9))),"")}

<tbody>
</tbody>

 

[mikerickson]

You could use a dynamic named range technique.

Instead of B1:B8, you could use (B1:INDEX(B:B, COUNTA(B:B), 1)

This works well with Names.

Name: LastRow
RefersTo: =COUNTA(Sheet1!$B:$B)

Name: Bcells
RefersTo = Sheet1!$B$1:INDEX(Sheet1!$B:$B, LastRow, 1)

Name: cCells
RefersTo = Sheet1!$C$1:INDEX(Sheet1!$C:$C, LastRow, 1)


The LastRow is used to insure that both ranges are the same size.

Your formula would become
{=IF(E9<>"",INDEX(cCells, SMALL(IF(bCells=E9,ROW(cCells)),COUNTIF(bCells))),"")}

 

[Aladin Akyurek]

I am having a similar problem.

Column B has the search field. Column C has the values to return. Cell E9 contains the search value.

To return the 2nd, then 3rd, then 4th value, the array formula would have to adjust depending on the last item found. I have tried to incorporate a Match() into it but could not get it to work. And also when I tried to insert a row above Row 1 all the formulas are in error then. It seems it should be a simple solution, but for the life of me I can't get it to work.

A	B	C	D	E	F	G
	2000	Apple
	1500	Monkey
	1750	Elephant
	2000	Dog
	2100	Orange
	2200	Banana
	2000	Bat
	2000	Bob
				2000	Wrong Answers	Correct Answers
					Apple	Apple	{=IF(E9<>"",INDEX($C$1:$C$8,SMALL(IF($B$1:$B$8=E9,ROW($C$1:$C$8)),COUNTIF(B$1:B3,E9))),"")}
					Dog	Dog	{=IF(E9<>"",INDEX($C$1:$C$8,SMALL(IF($B$1:$B$8=E9,ROW($C$1:$C$8)),COUNTIF(B$1:B4,E9))),"")}
					Dog	Bat	{=IF(E9<>"",INDEX($C$1:$C$8,SMALL(IF($B$1:$B$8=E9,ROW($C$1:$C$8)),COUNTIF(B$1:B5,E9))),"")}
					Bob	Bob	{=IF(E9<>"",INDEX($C$1:$C$8,SMALL(IF($B$1:$B$8=E9,ROW($C$1:$C$8)),COUNTIF(B$1:B8,E9))),"")}

<tbody>
</tbody>

In F10 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($C$1:$C$8,SMALL(IF($B$1:$B$8=$E$9,ROW($C$1:$C$8)-ROW($C$1)+1),
    ROWS($F$10:F10))),"")

 

[marlline]

Hi Everyone,

I need help here. To go from Input to Output as shown below, I use
=INDEX(DATA!D3:D6,,MATCH("Offer 1",Sheet2!$C$3:$C$6,0)1)

I could not retrieve #2 row data of Offer 2. Please help. Thank you very much!!

Input:

Offer	Campaign	# Participants	Offered
Offer 1	#1 ($10)	11	1
Offer 2	#2 ($50)	2	1
Offer 2	#2 ($75)	3	1
Offer 3	0	0	0

<tbody>
</tbody><colgroup><col><col><col span="2"></colgroup>

Output:

Offers	# Participants	Offered
Offer 1
#1 ($10)	11	1
Offer 1 Total	11	1
Offer 2
#2 ($50)	2	1
#2 ($75)	3	1
Offer 2 Total	5	1

<tbody>
</tbody><colgroup><col><col span="2"></colgroup>

In F10 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($C$1:$C$8,SMALL(IF($B$1:$B$8=$E$9,ROW($C$1:$C$8)-ROW($C$1)+1),
    ROWS($F$10:F10))),"")

 

[Aladin Akyurek]

@marline

In B10 enter and copy to C10:

=SUMIFS(C$2:C$5,$A$2:$A$5,$A$9,$B$2:$B$5,$A10)

In B14 enter, copy down to B15, and across:

=SUMIFS(C$2:C$5,$A$2:$A$5,$A$13,$B$2:$B$5,$A14)

 

[Aladin Akyurek]

@marline

In B10 enter and copy to C10:

=SUMIFS(C$2:C$5,$A$2:$A$5,$A$9,$B$2:$B$5,$A10)

In B14 enter, copy down to B15, and across:

=SUMIFS(C$2:C$5,$A$2:$A$5,$A$13,$B$2:$B$5,$A14)