Posted to microsoft.public.excel.worksheet.functions
|
|
Returning Numeric Results across a Single Row in Consecutive Cells
Just adjust the range being returned, after the INDEX
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:634b691170eff@uwe...
Hi Bob,
Thank you very much for your time and assistance. The Formula looks
awesome!
I've array entered the Formula but it actually returns the opposite to
what I
need. It returns the Numeric Labels that are in Row array $D$6:$BZ$6,
$D$16:
$BZ$16.
I am using the Formula below to Return the Results of Numeric Labels that
DO
NOT exist in the two Row arrays $D$6:$BZ$6, $D$16:$BZ$16:
=IF(AND(D3<$D$6:$BZ$6,D3<$D$16:$BZ$16),D3,"") - Array entered.
If time permits, would appreciate a version of your Formula that provides
the
Numeric Labels NOT in Row array $D$6:$BZ$6, $D$16:$BZ$16.
Cheers,
Sam
Bob Phillips wrote:
Use this array formula
=IF(ISERROR(SMALL(IF((ISNUMBER(MATCH(D3:Z3,$D$6:$ Z$6,0)))+(ISNUMBER(MATCH(D
3
:Z3,$D$16:$Z$16,0))),COLUMN($D$3:$Z$3)-MIN(COLUMN($D$3:$Z$3))+1,""),TRANSPO
S
E(ROW($A1:$A20)))),"",
INDEX(D3:Z3,1,SMALL(IF((ISNUMBER(MATCH(D3:Z3,$D$6 :$Z$6,0)))+(ISNUMBER(MATCH
(
D3:Z3,$D$16:$Z$16,0))),COLUMN($D$3:$Z$3)-MIN(COLUMN($D$3:$Z$3))+1,""),TRANS
P
OSE(ROW($A1:$A20)))))
Hi All,
[quoted text clipped - 20 lines]
Thanks
Sam
--
Message posted via http://www.officekb.com
|