# 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.  Register To Reply

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))))  Register To Reply

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)  Register To Reply

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.  Register To Reply

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.  Register To Reply