# Excel 2007 : Combining IF and VLOOKUP in a formula

1. ## 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.

2. ## 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. ## 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. ## 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.

5. ## Re: Combining IF and VLOOKUP in a formula

Originally Posted by NBVC
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.

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

#### 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