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

J

jeff106

New Member
Joined
Dec 2, 2016
Messages
21
[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 ?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Try this:

ABCDE
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.
 
Upvote 0
Eric W said:
Try this:

ABCDE
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.
Click to expand...


THIS SOLVED MY PROBLEM. THANK YOU!

PS. You saved the rest of my hair from being pulled out. :-)
 
Upvote 0
Happy to help! :cool:

And I'm always glad to be part of the hair preservation team!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
INDEX MATCH-MATCH Help
Replies
5
Views
238
surkdidat
S
J
  • Solved
INDEX-MATCH-MATCH help!
2
Replies
15
Views
297
henrik2h
H
W
  • Question Question
Index/Match Duplicate Values With No Helper Cells
Replies
3
Views
409
Domenic
D
C
  • Solved
VBA - Copy and paste a range of values N times
Replies
6
Views
440
cydmm
C
J
  • Solved
Using Index/Match (or Something Else) to Return Row(s) of Data
Replies
4
Views
167
jbennett01
J

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

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