V Look up
in [General]
|
Prev: Construct staff pay timesheet
Next: V Look Up
From: Don Guillett on 23 Apr 2010 08:40 If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "tonyagrey" <tonyagrey(a)discussions.microsoft.com> wrote in message news:B4463A87-1EFB-45FA-A069-964DA79D6291(a)microsoft.com... > Ok, > Sample from Column A of Cognos sheet > Acc No > 656918 > 650095 > 646211 > 716733 > 716297 > > Sample from Column A of other sheet > 524528 > 525036 > 551997 > 514710 > > ? > > -- > Tony > > > "JLatham" wrote: > >> The formula is structured properly. Typical reasons for "can't get the >> thing >> to work" are: >> you have numbers in column A, but numbers formatted as text on the Cognos >> sheet column A or vice-versa; >> you're comparing text and in one of the columns there may be added space >> characters in front/behind the text that aren't on the other sheet; >> the formula isn't being used properly - as the type of information to be >> matched is not in the first column of the lookup table. >> >> It would really help to know what kind of entries are in column A on both >> sheets. >> >> "tonyagrey" wrote: >> >> > WHats wrong with this formaula? Cant get the thing to work >> > >> > =VLOOKUP(A2,Cognos!$A$3:$J$763,2,FALSE) >> > -- >> > Tony
From: Don Guillett on 23 Apr 2010 11:20
You had several problems. Your numbers were not formatted as numbers I formatted properly(all numbers), set up a defined named range for the cognos sheet =OFFSET(Cognos!$A$2,1,0,COUNTA(Cognos!$A:$A),COUNTA(Cognos!$2:$2)) and wrote the proper vlookup formula. =IF(ISNA(VLOOKUP(A2,lookupcognos,4,0)),"",VLOOKUP(A2,lookupcognos,4,0)) You may move the cognos sheet back to the end if desired. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "Don Guillett" <dguillett1(a)gmail.com> wrote in message news:OmOd6Iu4KHA.5880(a)TK2MSFTNGP04.phx.gbl... > If desired, send your file to my address below. I will only look if: > 1. You send a copy of this message on an inserted sheet > 2. You give me the newsgroup and the subject line > 3. You send a clear explanation of what you want > 4. You send before/after examples and expected results. > > > -- > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguillett(a)gmail.com > "tonyagrey" <tonyagrey(a)discussions.microsoft.com> wrote in message > news:B4463A87-1EFB-45FA-A069-964DA79D6291(a)microsoft.com... >> Ok, >> Sample from Column A of Cognos sheet >> Acc No >> 656918 >> 650095 >> 646211 >> 716733 >> 716297 >> >> Sample from Column A of other sheet >> 524528 >> 525036 >> 551997 >> 514710 >> >> ? >> >> -- >> Tony >> >> >> "JLatham" wrote: >> >>> The formula is structured properly. Typical reasons for "can't get the >>> thing >>> to work" are: >>> you have numbers in column A, but numbers formatted as text on the >>> Cognos >>> sheet column A or vice-versa; >>> you're comparing text and in one of the columns there may be added space >>> characters in front/behind the text that aren't on the other sheet; >>> the formula isn't being used properly - as the type of information to be >>> matched is not in the first column of the lookup table. >>> >>> It would really help to know what kind of entries are in column A on >>> both >>> sheets. >>> >>> "tonyagrey" wrote: >>> >>> > WHats wrong with this formaula? Cant get the thing to work >>> > >>> > =VLOOKUP(A2,Cognos!$A$3:$J$763,2,FALSE) >>> > -- >>> > Tony > |