View original (French)

VLOOKUP or INDEX+MATCH

Solved
Lilibau Posted messages 32 Status Member -  
tobas Posted messages 210 Registration date   Status Member Last intervention   -
Hello the Community,

I want to use the VLOOKUP function to:

1/ Look up a user login (in one column)
2/ and display in the cell its last login date (in another column)

These are two different files but each file contains the login.

I tried the VLOOKUP function in several ways but the result is never correct. The result is either #N/A or the last login date of the row above my login, or the last login date of the login preceding the one searched.

I tried starting from 2 different sheets (with a try by renaming the sheets) and by copying the login data onto the same sheet, still without success.

I also tested the INDEX with MATCH but without success.

Do you have an idea or another formula that could work?

Example of the formula for a cell: =VLOOKUP("login";LookupRange;DateLastLoginColumn). When I add the approximate match (i.e. FALSE to get an exact value), the result is #N/A.

Thank you in advance for your help.

Best regards
Lilibau

7 answers

  1.
    tobas Posted messages 210 Registration date   Status Member Last intervention   24
     
    Hello,
    for a VLOOKUP, your lookup value must be in the first column of your range
    otherwise provide an example file to format your formula
    0
  2. WeaponEDGE
     
    Hello,

    With vertical search you have to be vigilant.

    First, make sure that your search criterion is located in the first column of your two tables.

    Second, ensure that the data is scrupulously identical. Sometimes there are trailing spaces, and as a result your search criterion is different from that of your reference table.
    0
  3. Lilibau Posted messages 32 Status Member
     
    Thank you Tobas and WeaponEDGE.

    I had started the reference table at the column of my login (column C), so I removed the two previous columns to make the login column the first one.

    I tried to copy you into a workbook an example because the files I work with are classified internal to my company (confidentiality reason). In the new workbook, the formula works without any problem!!!! but in my file it only applies to the first row (which is already a start) but as soon as I copy to the other cells, the result again shows the login connection date of the previous one searched for!!

    Could another formula be applied?

    Thanks again to both of you.
    0
    1. Lilibau Posted messages 32 Status Member
       
      Sorry, I can’t find the function to add a document to my reply anymore.
      0
    2. tobas Posted messages 210 Registration date   Status Member Last intervention   24
       
      Utilise le site cjoint.com
      0
    3. Lilibau Posted messages 32 Status Member
       
      Thank you Tobas. Example: https://www.cjoint.com/?3HgnV0CfI1n However, with this model the formula works but still not in my original workbook. The difference is that the example is on about ten rows, while the original workbook has 1500 rows! The formula calculates but does not return the correct result: I get the last connection date from row B (for example) whereas the correct information is on row C.
      0
    5. Lilibau Posted messages 32 Status Member
       
      Thank you Tobas, but applying your formula only the first line is (still) correct; for the rest of the lines it shows N/A

      I did a simulation on the file by adding a new column and applying the same formula as the one you modified: https://www.cjoint.com/?3HgoLkJxYg4

      I think VLOOKUP does not apply to my case ... I don’t understand where the error could come from.

      Thank you
      0
  4. miniman
     
    Hello

    shouldn’t you sort the date and time column in descending order by selecting columns A and B of the Connections tab

    as soon as the formula finds a login it takes the first line read, so not necessarily the latest date

    on Excel 2002 there is no error code in the sheet!
    0
    1. Lilibau Posted messages 32 Status Member
       
      Hello Miniman, Unfortunately the sort on the "last connection date" column doesn't work (perhaps because of the presence of "à" in the field). example of last connection date 23/04/2013 at 13:41 25/10/2012 at 11:17 17/06/2013 at 15:04 I did another test using Convert to separate the date and the time, then I sorted by the "Last connection date" column (descending sort). results: for the first row the displayed result is correct (!!!) but if I copy to the other rows I get N/A again. If I then copy the formula identically (and manually) to the second row I still get N/A. It's enough to drive you to Latin, if I may say so !!!
      0
  6. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Hello everyone,

    if you have #N/A! it means the login is not found in column A.
    Search it manually (or with Ctrl+F) and look at the difference. There is necessarily one.
    In the 2nd post WeaponEDGE you had already warned about this...

    And if a login has several connections (to be specified, you still haven't done it), you won't be able to get the latest one with VLOOKUP.

    eric

    You will never respond to an unsolicited private message...
    Well, that’s done.
    0
  7. Lilibau Posted messages 32 Status Member
     
    Hello everyone,

    Finally we’ve had the last word!!!!!!

    Eric, thanks for your last reply. I had taken into account WeaponEDGE’s remark: the logins are indeed "identical" between the two databases (the user file base and the login journal base). For example: "aaccueil" in one base and "aaccueil" in the other.

    However, the exports of the two sheets I’m working with are not from the same bases, which seems to be the source of my N/A.

    I tried copying/pasting the login from one base onto the second and the result is displayed. With the naked eye there’s no difference, nor a space before the login, so the space must be after the login?

    The problem is I can’t apply a VLOOKUP formula to copy/paste the logins from one base to the other.

    Thanks again Tobas for all your advice.

    Have a great day everyone and thanks again.

    Lilibau
    0
  8. Lilibau Posted messages 32 Status Member
     
    It's okay !!!!

    I applied the SUPPRESPACE formula to my 2 "logins" columns and it works !!!!

    Thanks to everyone.
    0
    1. tobas Posted messages 210 Registration date   Status Member Last intervention   24
       
      we're happy for you
      0