Results 1 to 5 of 5

Thread: Lookup On Multiple Columns And Return Result Using Match Index

  1. #1
    Junior Member
    Join Date
    Apr 2013
    Posts
    6
    Rep Power
    0

    Lookup On Multiple Columns And Return Result Using Match Index

    In my workbook I have 2 sheets named Project costing and salaries .Each sheet has 1 table . i need to compare Resource Name and Month in Table in 'Project costing' sheet with that in another table -Table 8 in 'Salaries' sheet. If 'Resource Name + Month' combination is matching in both tables, then formulae has to return CTC (Another coloumn in Table 8) figure in corresponding row in Table 8.Following is the formulae I used. But it is returning #VALUE

    INDEX(Table8[[#Headers],[CTC]],MATCH('Entry | Project costing'!B4&'Entry | Project costing'!D4,Table8[[#Headers],[Team]]&Table8[[#Headers],[Month]],0))

    B4 has resource name and D4 has Month entry in the table in project costing sheet. 'Team' coloumn has resourse name and 'Month' coloumn has month entry in Table 8 in 'Salaries' sheet. Can we use logical connectives in match function as I have done?

    Please help. I have been pondering with this days together…
    Attached Files Attached Files
    Last edited by nikhil83; 04-23-2013 at 05:28 PM.

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Would be best if you can post a sample database with the expected output
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Apr 2013
    Posts
    6
    Rep Power
    0
    I have done as you told..please review and get back

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    K4=INDEX(Salaries!$F$5:$F$10,SUMPRODUCT(MATCH(B4&D 4,Salaries!$B$5:$B$10&Salaries!$D$5:$D$10,0))) and drag down
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Junior Member
    Join Date
    Apr 2013
    Posts
    6
    Rep Power
    0
    Thank you so much for the prompt reply.....
    Last edited by nikhil83; 04-24-2013 at 11:54 AM.

Similar Threads

  1. Replies: 30
    Last Post: 04-15-2019, 07:36 PM
  2. Replies: 3
    Last Post: 05-23-2013, 11:17 PM
  3. Replies: 16
    Last Post: 04-19-2013, 08:20 PM
  4. Find the highest then lookup result
    By Stalker in forum Excel Help
    Replies: 4
    Last Post: 04-02-2013, 02:04 PM
  5. Replies: 3
    Last Post: 03-12-2013, 12:54 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •