Index, Match and Does Not Equal To

[nicolakc]

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

 

[martindwilson]

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]

Try something like this...

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

 

[martindwilson]

first one (index)gives first non match
second one(lookup) gives last non match

 

[nicolakc]

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]

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

 

[nicolakc]

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

Hi Martin,

Here is an example:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Bella[/TD]
[TD]VIC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Sandgate[/TD]
[TD]NSW[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Karingal[/TD]
[TD]QLD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Allura[/TD]
[TD]VIC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]VIC[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]=INDEX(A1:A4,MATCH(A6,b1:b4,0),)
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]=INDEX(A1:A4,MATCH(A6,b1:b4,0),)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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]

do you mean this
Sheet2

*	A	B	C	D	E	F	G	H	I	J
Bella	VIC	*	VIC	Bella	*	note a2:a10 is 9 rows	*	*	*
Sandgate	NSW	*	*	Allura	*	hence	*	*	*
Karingal	QLD	*	*	*	*	ROW($1:$9) is used in the formula	*	*	*
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>
[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

</tbody>

Spreadsheet Formulas

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