+ Reply to Thread
Results 1 to 5 of 5

Excel 2007 : Combining IF and VLOOKUP in a formula

  1. #1
    Registered User
    Join Date
    09-06-2011
    Location
    Illinois USA
    MS-Off Ver
    Excel 2003
    Posts
    3

    Combining IF and VLOOKUP in a formula

    I want to extract data from one spreadsheet into another using the IF and VLOOKUP functions. One spreadsheet contains the names with empty cells(Master Sheet) and the other contains the names with the data in a variety of columns, but on the same row for each employee(Balances) I need to have it on the same rown in the Master Sheet for each employee. Can that be done? I've attached a sample copy.
    Attached Files Attached Files
    Last edited by Lezahp; 09-07-2011 at 10:41 AM. Reason: Additional Information....using Excel 2007

  2. #2
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,282

    Re: Combining IF and VLOOKUP in a formula

    Try this array formula

    =SUM(IF(Balances!$B$2:$B$20=$A3,IF(Balances!$C$2:$O$20<>"",IF(Balances!$C$2:$O$20=C$2,Balances!$D$2:$P$20))))

  3. #3
    Registered User
    Join Date
    09-06-2011
    Location
    Illinois USA
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Combining IF and VLOOKUP in a formula

    Not working. it's giving me totals. I only need the value from each cell for each employee. (Sick Leave Balance, Vacation Leave Balance, etc)

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Combining IF and VLOOKUP in a formula

    You need to confirm the formula given with CTRL+SHIFT+ENTER then copy down and across.

    Alternatively, this should also work:

    =SUMPRODUCT((Balances!$B$2:$B$20=$A3)*(Balances!$C$2:$O$20=C$2),Balances!$D$2:$P$20)

    confirmed only with ENTER as normal

    note, the headers in C2:E2 should match the categories in the Balances sheet exactly also.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  5. #5
    Registered User
    Join Date
    09-06-2011
    Location
    Illinois USA
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Combining IF and VLOOKUP in a formula

    Quote Originally Posted by NBVC View Post
    You need to confirm the formula given with CTRL+SHIFT+ENTER then copy down and across.

    Alternatively, this should also work:

    =SUMPRODUCT((Balances!$B$2:$B$20=$A3)*(Balances!$C$2:$O$20=C$2),Balances!$D$2:$P$20)

    confirmed only with ENTER as normal

    note, the headers in C2:E2 should match the categories in the Balances sheet exactly also.
    SOLVED!!!!
    Thanks so much.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1