Re: [libreoffice-users] VLOOKUP Problem

Okay, I was too quick there. See corrections below.

2013/2/2 Johnny Rosenberg <gurus.knugum@gmail.com>:
> 2013/2/2 John Meyer <john.l.meyer@gmail.com>:
> > I have two columns.  Column A displays the logon names being used and
> > Column B contains all possible logons (technically it's on another sheet,
> > but I'll simplify).  What I want to appear on column C is a list of all
> > logons that are not being used.  For instance:
> >
> > COLUMNA:
> >
> > Frank
> > Harry
> > George
> >
> > COLUMNB
> > Frank
> > Harry
> > Jason
> > George
> > Steven
> >
> >
> > So what I want to appear in column c is:
> >
> > Jason
> > Steven
>
> I did some further testing and came up with an answer that requires
> several columns (you can always hide those you don't want to see):
>
> So in column A, enter those three names, starting at A1.
>
> In column B, enter those five names, starting at B1.
>
> In C1:
> =IF(B1="";"";IF(ISNA(MATCH(B1;$A$1:$A$10000;0));1;0))
>
> In D1:
> =MATCH(1;$C$1:$C$40)
> In D2:
> =MATCH(1;INDIRECT(ADDRESS(D1+1;3)):$C$40)+D1


Won't work. Correction:
D1: =MATCH(1;$C$1:$C$40;0)

D2: =MATCH(1;INDIRECT(ADDRESS(D1+1;3)):$C$40;0)+D1

In both cases, I somehow forgot the last parameter in MATCH: 0.
MATCH(What;Where;Type)
Omitting Type → Type=1 which is not good in this case. Type=0 is what's needed.

I hope I didn't confuse you too much…

Johnny Rosenberg


> In E1:
> =IF(ISNA(D1);"";INDIRECT(ADDRESS(D1;2)))
>
> Now copy C1 down as far as you want (max C10000, or you need to edit
> the formula slightly).
> Copy D2 down as far as you want (max C10000 if you don't to the
> appropriate editing if C1).
> Copy E1 down, the same way as C1.
> Hide columns C and D.
> Done. Clumsy, but done…
>
>
> Johnny Rosenberg

-- 
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted