V Look up
in [General]
|
Prev: Construct staff pay timesheet
Next: V Look Up
From: Dave Peterson on 23 Apr 2010 08:01 Debra Dalgleish has lots of notes on troubleshooting =vlookup(): http://contextures.com/xlFunctions02.html#Trouble If that doesn't help, you'll want to share more details about wrong means. tonyagrey wrote: > > WHats wrong with this formaula? Cant get the thing to work > > =VLOOKUP(A2,Cognos!$A$3:$J$763,2,FALSE) > -- > Tony -- Dave Peterson
From: Dave Peterson on 23 Apr 2010 08:02 If your values are digits, then it's not enough to just change the format of the cell. You'll have to change the entry, too. There are ways of doing that, but you'll have to share more info. tonyagrey wrote: > > 1. Currently returning #NA > 2. A2 is definitely available i have manually checked it. > 3. I have formatted the text in both sheets so that they are the same . > > Still no luck. Any other suggestions? > -- > Tony > > "Jacob Skaria" wrote: > > > You formula is correct. > > > > 1. Currently what does that return... > > > > 2. Check whether A2 is available in A3:A763. If so check whether it is > > spelled exactly same as that of cell A2. (without any extra spaces) > > > > 3. If A2 is not found the formula returns #NA error. If you are looking to > > handle this then use ISNA() with IF() to handle this as below ... > > =IF(ISNA(VLOOKUP(A2,Cognos!$A$3:$J$763,2,0)),"", > > VLOOKUP(A2,Cognos!$A$3:$J$763,2,FALSE)) > > > > -- > > Jacob (MVP - Excel) > > > > > > "tonyagrey" wrote: > > > > > WHats wrong with this formaula? Cant get the thing to work > > > > > > =VLOOKUP(A2,Cognos!$A$3:$J$763,2,FALSE) > > > -- > > > Tony -- Dave Peterson
From: tonyagrey on 23 Apr 2010 08:04 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: JLatham on 23 Apr 2010 08:19 This looks like the "numbers in one column, numbers as text in the other" situation. One way to 'fix' this is to make sure that all are numbers in both columns. Start by formatting those columns as General. Then perform this operation on both sheets, one at a time: pick an unused cell and enter the number 1 into it. Chose that cell and Copy it. Select all of the account numbers in column A on that sheet and use: Edit --> Paste Special and select the "Multiply" option and press [OK]. That will force the numbers-as-text to become real numbers. You can clear out the cell that you entered the 1 into after you're done with each sheet. "tonyagrey" wrote: > 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: Pete_UK on 23 Apr 2010 08:21
Here's some variations that you can try: =VLOOKUP(A2&"",Cognos!$A$3:$J$763,2,FALSE) and: =VLOOKUP(A2*1,Cognos!$A$3:$J$763,2,FALSE) The first one converts your lookup value in A2 into text to match with text entries in column A of the Cognos sheet. The second one converts A2 into a numeric value in case column A of the Cognos sheet is treated as numbers. Another common problem is that you might have spaces or non-breaking space characters in column A of the Cognos sheet which you won't be able to see. You will need to remove these in order to get exact matches, or you could use a widcard character in the formula: =VLOOKUP(A2&"*",Cognos!$A$3:$J$763,2,FALSE) Hope this helps. Pete On Apr 23, 1:04 pm, tonyagrey <tonyag...(a)discussions.microsoft.com> wrote: > 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- Hide quoted text - > > - Show quoted text - |