index/match duplicate value help (also with #N/A error

[jeff106]

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Store A[/TD]
[TD="align: center"]95[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Store C[/TD]
[TD="align: center"]91[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Store B[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Store C[/TD]
[TD="align: center"]91[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Store C[/TD]
[TD="align: center"]91[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Store E[/TD]
[TD="align: center"]93[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Store D[/TD]
[TD="align: center"]91[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Store E[/TD]
[TD="align: center"]93[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hi All,
I have a sheet that is using index match to pull data (column B) and match it to restaurant names (Column A) from another sheet. However, in some instances there is no data for that restaurant so the index match returns #N/A.

I want to be able to then take that data an in a separate area show the bottom (and top) performing restaurants for that data. (Columns D & E). In cell E1, I am using this formula to show me the lowest number in column B (as long as its below 95 because stores performing above 95 shouldn't be listed in the "bottom performing" list).

=SMALL(IF(ISERROR(B1:B5), "", IF(B1:B5<=95,B1:B5,"")),1)

Cell E2 would use the same formula but change the "1" at the very end to 2 to show me the second smallest number. The IF ISERROR is helping me to ignore the #N/A when listing these numbers for me.

It works great. Now the next step is where I am having an issue. I would like D1 and D2 to show the Store Name that corresponds to those 2 lowest data values. When using index match however,

=INDEX($A$1:$A$5,MATCH($E1,$B$1:$B$5,0))
=INDEX($A$1:$A$5,MATCH($E2,$B$1:$B$5,0))

it only lists the first store name (Store C) twice since the 2 lowest values are equal instead of first and second lowest store names (Store C & D). I have researched several different solutions on the web like ranking the values before using index match. However, I can't use the rank feature because of the #N/A values.

Any idea how I can get the correct store names listed with the lowest values ?

 

[Eric W]

Try this:

	A	B	C	D	E
Store A
Store B
Store C
Store D
Store E

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]95[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Store C[/TD]
[TD="align: center"]91[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]#N/A[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Store D[/TD]
[TD="align: center"]91[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]91[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Store E[/TD]
[TD="align: center"]93[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]91[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]93[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]{=IF(E1<>"",INDEX($A$1:$A$5,SMALL(IF(ISNUMBER($B$1:$B$5),IF($B$1:$B$5=E1,ROW($B$1:$B$5)-ROW($B$1)+1)),COUNTIF($E$1:$E1,E1))),"")}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E1[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF(ISERROR($B$1:$B$5), "", IF($B$1:$B$5<95,$B$1:$B$5,"")),ROWS($E$1:$E1)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Put those 2 array formulas in D1:E1, then copy down as needed.

Hope this helps.

 

[jeff106]

Try this:

	A	B	C	D	E
Store A
Store B
Store C
Store D
Store E

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]95[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Store C[/TD]
[TD="align: center"]91[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]#N/A[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Store D[/TD]
[TD="align: center"]91[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]91[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Store E[/TD]
[TD="align: center"]93[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]91[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]93[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]{=IF(E1<>"",INDEX($A$1:$A$5,SMALL(IF(ISNUMBER($B$1:$B$5),IF($B$1:$B$5=E1,ROW($B$1:$B$5)-ROW($B$1)+1)),COUNTIF($E$1:$E1,E1))),"")}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E1[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF(ISERROR($B$1:$B$5), "", IF($B$1:$B$5<95,$B$1:$B$5,"")),ROWS($E$1:$E1)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Put those 2 array formulas in D1:E1, then copy down as needed.

Hope this helps.

THIS SOLVED MY PROBLEM. THANK YOU!

PS. You saved the rest of my hair from being pulled out.

 

[Eric W]

Happy to help!

And I'm always glad to be part of the hair preservation team!