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:

	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]

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>

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