Array Question

K

keeblerjp

I have this array...
=INDEX($A$1:$A$92,SMALL(IF(ISNA(MATCH($A$1:$A$91,RoomList!$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.
 
P

Peo Sjoblom

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
 
K

keeblerjp

So any suggestions on what i could put?

Peo Sjoblom said:
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
Click to expand...
 
D

Domenic

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!
 
K

keeblerjp

Works like a charm! Thanks

Domenic said:
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!
Click to expand...
 
Top