I have this array...
=INDEX($A$1:$A$92,SMALL(IF(ISNA(MATCH($A$1:$A$91,R oomList!$E$11:$E$44,0)),ROW($A$1:$A$91),92),ROW(1: 91))) & ""

But it only reads one column the $E$11:$E$44 but i keep trying to get it to
read the column next to it, but it does not like $E$11:$F$44 in the formula.

You can't use MATCH with multiple columns

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"keeblerjp" wrote in message
...
I have this array...
=INDEX($A$1:$A$92,SMALL(IF(ISNA(MATCH($A$1:$A$91,R oomList!$E$11:$E$44,0)),ROW($A$1:$A$91),92),ROW(1: 91)))
& ""

But it only reads one column the $E$11:$E$44 but i keep trying to get it
to
read the column next to it, but it does not like $E$11:$F$44 in the
formula.

So any suggestions on what i could put?

"Peo Sjoblom" wrote:

You can't use MATCH with multiple columns

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"keeblerjp" wrote in message
...
I have this array...
=INDEX($A$1:$A$92,SMALL(IF(ISNA(MATCH($A$1:$A$91,R oomList!$E$11:$E$44,0)),ROW($A$1:$A$91),92),ROW(1: 91)))
& ""

But it only reads one column the $E$11:$E$44 but i keep trying to get it
to
read the column next to it, but it does not like $E$11:$F$44 in the
formula.

Try...

B1, copied down:

=INDEX($A$1:$A$92,SMALL(IF(COUNTIF(RoomList!$E$11: $F$44,$A$1:$A$92)=0,ROW
($A$1:$A$92)-ROW($A$1)+1),ROWS($B$1:B1)))

....confirmed with CONTROL+SHIFT+ENTER. Adjust the range accordingly.

Hope this helps!

In article ,
keeblerjp wrote:

I have this array...
=INDEX($A$1:$A$92,SMALL(IF(ISNA(MATCH($A$1:$A$91,R oomList!$E$11:$E$44,0)),ROW(
$A$1:$A$91),92),ROW(1:91))) & ""

But it only reads one column the $E$11:$E$44 but i keep trying to get it to
read the column next to it, but it does not like $E$11:$F$44 in the formula.

Works like a charm! Thanks

"Domenic" wrote:

Try...

B1, copied down:

=INDEX($A$1:$A$92,SMALL(IF(COUNTIF(RoomList!$E$11: $F$44,$A$1:$A$92)=0,ROW
($A$1:$A$92)-ROW($A$1)+1),ROWS($B$1:B1)))

....confirmed with CONTROL+SHIFT+ENTER. Adjust the range accordingly.

Hope this helps!

In article ,
keeblerjp wrote:

I have this array...
=INDEX($A$1:$A$92,SMALL(IF(ISNA(MATCH($A$1:$A$91,R oomList!$E$11:$E$44,0)),ROW(
$A$1:$A$91),92),ROW(1:91))) & ""

But it only reads one column the $E$11:$E$44 but i keep trying to get it to
read the column next to it, but it does not like $E$11:$F$44 in the formula.