Excel 2007 : Combining IF and VLOOKUP in a formula

**Lezahp** · 09-07-2011, 10:41 AM

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.

**Bob Phillips** · 09-07-2011, 10:51 AM

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

**Lezahp** · 09-07-2011, 11:02 AM

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)

**NBVC** · 09-07-2011, 11:12 AM

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.

**Lezahp** · 09-07-2011, 11:51 AM

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.