VLOOKUP and Match formula while doing copy and paste

  • Hello Friends


    Want to lookup vaue from a data base list, there are 2 unique id in the database 1. date and 2. account number. below is the screen shot of data base.



    [TABLE="width: 217"]

    [tr]


    [td]

    Acct.No

    [/td]


    [td]

    Value

    [/td]


    [td]

    Date

    [/td]


    [/tr]


    [tr]


    [td]

    01234

    [/td]


    [TD="align: right"]12546[/TD]
    [TD="align: right"]1-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    012345

    [/td]


    [TD="align: right"]6589[/TD]
    [TD="align: right"]1-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    01254

    [/td]


    [TD="align: right"]74589[/TD]
    [TD="align: right"]1-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    02589

    [/td]


    [TD="align: right"]35894[/TD]
    [TD="align: right"]1-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    09854

    [/td]


    [TD="align: right"]12587[/TD]
    [TD="align: right"]1-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    0978

    [/td]


    [TD="align: right"]48963[/TD]
    [TD="align: right"]1-Apr-12[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    01234

    [/td]


    [TD="align: right"]12548[/TD]
    [TD="align: right"]2-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    012345

    [/td]


    [TD="align: right"]6580[/TD]
    [TD="align: right"]2-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    01254

    [/td]


    [TD="align: right"]4589[/TD]
    [TD="align: right"]2-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    02589

    [/td]


    [TD="align: right"]3589[/TD]
    [TD="align: right"]2-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    09854

    [/td]


    [TD="align: right"]12578[/TD]
    [TD="align: right"]2-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    0978

    [/td]


    [TD="align: right"]38963[/TD]
    [TD="align: right"]2-Apr-12[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    01234

    [/td]


    [TD="align: right"]22548[/TD]
    [TD="align: right"]3-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    012345

    [/td]


    [TD="align: right"]65852[/TD]
    [TD="align: right"]3-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    01254

    [/td]


    [TD="align: right"]45891[/TD]
    [TD="align: right"]3-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    02589

    [/td]


    [TD="align: right"]58985[/TD]
    [TD="align: right"]3-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    09854

    [/td]


    [TD="align: right"]22578[/TD]
    [TD="align: right"]3-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    0978

    [/td]


    [TD="align: right"]48963[/TD]
    [TD="align: right"]3-Apr-12[/TD]

    [/tr]


    [/TABLE]


    I get the raw data in the above manner, i have to arrange the data in the below manner for reporting. on a daily i have to copy paste, to get rid of this i tried using vlookup and match formula, however it worked only for the 1-april-12, formula did not worked when i copied and pasted for other dates. my formula is


    =VLOOKUP(I15,$C$14:$E$19,MATCH($G$15,$D$14:$D$19,1))


    [TABLE="width: 727"]

    [tr]


    [td]

    Date

    [/td]


    [td][/td]


    [td]

    Acct.no

    [/td]


    [td]

    Value

    [/td]


    [td][/td]


    [td]

    Acct.no

    [/td]


    [td]

    Value

    [/td]


    [td][/td]


    [td]

    Acct.no

    [/td]


    [td]

    Value

    [/td]


    [td][/td]


    [td]

    Acct.no

    [/td]


    [td]

    Value

    [/td]


    [td][/td]


    [td]

    Acct.no

    [/td]


    [td]

    Value

    [/td]


    [td][/td]


    [td]

    Acct.no

    [/td]


    [td]

    Value

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]1-Apr-12[/TD]

    [td][/td]


    [td]

    01234

    [/td]


    [TD="align: right"]12546[/TD]

    [td][/td]


    [td]

    012345

    [/td]


    [TD="align: right"]6589[/TD]

    [td][/td]


    [td]

    01254

    [/td]


    [TD="align: right"]74589[/TD]

    [td][/td]


    [td]

    02589

    [/td]


    [TD="align: right"]35894[/TD]

    [td][/td]


    [td]

    09854

    [/td]


    [TD="align: right"]12587[/TD]

    [td][/td]


    [td]

    0978

    [/td]


    [TD="align: right"]35894[/TD]

    [/tr]


    [tr]


    [TD="align: right"]2-Apr-12[/TD]

    [td][/td]


    [td]

    01234

    [/td]


    [TD="align: center"]#REF![/TD]

    [td][/td]


    [td]

    012345

    [/td]


    [TD="align: center"]#REF![/TD]

    [td][/td]


    [td]

    01254

    [/td]


    [td][/td]


    [td][/td]


    [td]

    02589

    [/td]


    [td][/td]


    [td][/td]


    [td]

    09854

    [/td]


    [td][/td]


    [td][/td]


    [td]

    0978

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]3-Apr-12[/TD]

    [td][/td]


    [td]

    01234

    [/td]


    [td][/td]


    [td][/td]


    [td]

    012345

    [/td]


    [td][/td]


    [td][/td]


    [td]

    01254

    [/td]


    [td][/td]


    [td][/td]


    [td]

    02589

    [/td]


    [td][/td]


    [td][/td]


    [td]

    09854

    [/td]


    [td][/td]


    [td][/td]


    [td]

    0978

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]4-Apr-12[/TD]

    [td][/td]


    [td]

    01234

    [/td]


    [td][/td]


    [td][/td]


    [td]

    012345

    [/td]


    [td][/td]


    [td][/td]


    [td]

    01254

    [/td]


    [td][/td]


    [td][/td]


    [td]

    02589

    [/td]


    [td][/td]


    [td][/td]


    [td]

    09854

    [/td]


    [td][/td]


    [td][/td]


    [td]

    0978

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]5-Apr-12[/TD]

    [td][/td]


    [td]

    01234

    [/td]


    [td][/td]


    [td][/td]


    [td]

    012345

    [/td]


    [td][/td]


    [td][/td]


    [td]

    01254

    [/td]


    [td][/td]


    [td][/td]


    [td]

    02589

    [/td]


    [td][/td]


    [td][/td]


    [td]

    09854

    [/td]


    [td][/td]


    [td][/td]


    [td]

    0978

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]6-Apr-12[/TD]

    [td][/td]


    [td]

    01234

    [/td]


    [td][/td]


    [td][/td]


    [td]

    012345

    [/td]


    [td][/td]


    [td][/td]


    [td]

    01254

    [/td]


    [td][/td]


    [td][/td]


    [td]

    02589

    [/td]


    [td][/td]


    [td][/td]


    [td]

    09854

    [/td]


    [td][/td]


    [td][/td]


    [td]

    0978

    [/td]


    [td][/td]


    [/tr]


    [/TABLE]


    Have attached the excel sheet for reference. can anyone suggest correct formula or correct way to do this.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!