# Index, Match and Does Not Equal To

#### nicolakc

##### New Member
Hi all,

Just wondering if anyone knows how to return a value in index and match, that is not equal to a value in another cell or range of cells. My current formula is:

=INDEX(\$C\$6:\$C\$9,MATCH(\$B\$27,\$D\$6:\$D\$9,0),)

I need to somehow embed a <> function in here.

I have researched lookup, match and index and can't find the answer anywhere - help!

Thanks...

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### martindwilson

##### Well-known Member
depends
=INDEX(\$C\$6:\$C\$9,MATCH(TRUE,INDEX(\$D\$6:\$D\$9<>B27,0),0))
would find the first match that doesn't = b27

#### AlphaFrog

##### MrExcel MVP
Try something like this...

=LOOKUP(TRUE, \$B\$27<>\$D\$6:\$D\$9, \$C\$6:\$C\$9)

#### martindwilson

##### Well-known Member
first one (index)gives first non match
second one(lookup) gives last non match

#### nicolakc

##### New Member
depends
=INDEX(\$C\$6:\$C\$9,MATCH(TRUE,INDEX(\$D\$6:\$D\$9<>B27,0),0))
would find the first match that doesn't = b27

Hi, this is very helpful, but I need it to find the first result that doesn't = C6, but it still needs to match B27.

#### martindwilson

##### Well-known Member
i think you need to give a better example of your data

#### nicolakc

##### New Member
i think you need to give a better example of your data

Hi Martin,

Here is an example:

 A B C 1 Bella VIC 2 Sandgate NSW 3 Karingal QLD 4 Allura VIC 5 6 VIC 7 8 =INDEX(A1:A4,MATCH(A6,b1:b4,0),) 9 =INDEX(A1:A4,MATCH(A6,b1:b4,0),)

<tbody>
</tbody>

So I want the result in A8 to equal Bella, but I want the result in A9 to equal Allura, because Bella has already been used.

Thanks again!

#### martindwilson

##### Well-known Member
do you mean this
Sheet2

 * A B C D E F G H I J 2 Bella VIC * VIC Bella * note a2:a10 is 9 rows * * * 3 Sandgate NSW * * Allura * hence * * * 4 Karingal QLD * * * * ROW(\$1:\$9) is used in the formula * * * 5 Allura VIC * * * * * * * *

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

 Cell Formula E2 {=IFERROR(INDEX(\$A\$2:\$A\$10,SMALL(IF(\$B\$2:\$B\$10=\$D\$2,ROW(\$1:\$9),""),ROWS(\$1:1))),"")} E3 {=IFERROR(INDEX(\$A\$2:\$A\$10,SMALL(IF(\$B\$2:\$B\$10=\$D\$2,ROW(\$1:\$9),""),ROWS(\$1:2))),"")}

<tbody>
</tbody>
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

if you are pre excel 2007 use this version
=IF(ROWS(\$1:1)>COUNTIF(\$B\$2:\$B\$10,\$D\$2),"",INDEX(\$A\$2:\$A\$10,SMALL(IF(\$B\$2:\$B\$10=\$D\$2,ROW(\$1:\$9),""),ROWS(\$1:1)))) again array entered

Last edited:

Replies
5
Views
242
Replies
3
Views
59
Replies
1
Views
166
Replies
3
Views
306
Replies
3
Views
109

1,191,589
Messages
5,987,518
Members
440,099
Latest member
wai2kit

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

### Which adblocker are you using?

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

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