VLOOKUP + IF formula???

September 27, 2010 at 23:55:51
Specs: Windows XP 2002
Hi all,

I'm fairly inexperienced with advanced excel functions, so this might be a ridiculously simple problem to solve. But I have 2 worksheets and I need to populate data in one worksheet based on information found in the second. Worksheet 2 has multiple row entries for the same person, but I'm looking for the row with the total amount.

Worksheet 1 looks like this:
Employee Name Employee Total Employer Total
Doe, Jane

The columns for employee total and employer total are blank. I need to pull the corresponding value from worksheet 2, which looks like this:

Doe, Jane EMPLOYEE PRE-TAX 96.15 05/06/2010
Doe, Jane EMPLOYEE PRE-TAX 96.15 05/20/2010
Doe, Jane EMPLOYEE PRE-TAX 96.15 06/03/2010
Doe, Jane EMPLOYEE PRE-TAX 96.15 06/17/2010
Doe, Jane EMPLOYEE TOTAL 384.60


Doe, Jane EMPLOYER MATCH 307.69 05/06/2010
Doe, Jane EMPLOYER MATCH 307.69 05/20/2010
Doe, Jane EMPLOYER MATCH 307.69 06/03/2010
Doe, Jane EMPLOYER MATCH 307.69 06/17/2010
Doe, Jane EMPLOYER TOTAL 1,230.76

So, I need a formula that will match the name from both worksheets, and populate '384.60' into worksheet 1 under the Employee Total column and '1,230.76' under the Employer Total column.
Not sure what formula to use, but I'm thinking a combination of Vlookup + IF?

Make sense? Any help would be appreciated!

Thanks,
Linda


See More: VLOOKUP + IF formula???

Report •


#1
September 28, 2010 at 05:00:03
How many rows separate each block of data on Sheet 2?

I'm not sure if it matters since I haven't really tried anything yet, but I want to make sure that I set up a sheet that matches yours exactly.


Report •

#2
September 28, 2010 at 08:19:09
If your Sheet 1 looks like this:

       A                B              C
1) Employee Name  EMPLOYEE TOTAL   EMPLOYER TOTAL
2) Doe, Jane          384.6           1230.76

and your Sheet 2 looks like this:

    A              B             C        D
Doe, Jane  EMPLOYEE PRE-TAX    96.15  5/6/2010
Doe, Jane  EMPLOYEE PRE-TAX    96.15  5/20/2010
Doe, Jane  EMPLOYEE PRE-TAX    96.15  6/3/2010
Doe, Jane  EMPLOYEE PRE-TAX    96.15  6/17/2010
Doe, Jane  EMPLOYEE TOTAL     384.60
	 		

Doe, Jane  EMPLOYER MATCH     307.69  5/6/2010
Doe, Jane  EMPLOYER MATCH     307.69  5/20/2010
Doe, Jane  EMPLOYER MATCH     307.69  6/3/2010
Doe, Jane  EMPLOYER MATCH     307.69  6/17/2010
Doe, Jane  EMPLOYER TOTAL   1,230.76

Then on Sheet 1 Cell B2 enter the Array formula:

=MAX(IF(Sheet2!$A$1:$A$12=A2,IF(Sheet2!$B$1:$B$12=$B$1,Sheet2!$C$1:$C$12)))

and on Sheet 1 Cell C2 enter the Array formula:

=MAX(IF(Sheet2!$A$1:$A$12=A2,IF(Sheet2!$B$1:$B$12=$C$1,Sheet2!$C$1:$C$12)))

For an ARRAY formula, you DO NOT press the ENTER key.....
After typing/copying the formula, press CTRL-SHIFT-ENTER, you will see curly brackets surrounding the formula.
They should look like:

{=MAX(IF(Sheet2!$A$1:$A$12=A2,IF(Sheet2!$B$1:$B$12=$B$1,Sheet2!$C$1:$C$12)))}

This solution is a bit of a cheat, in that it is looking for the highest amounts associated with the Employee Name and either the Employee Total or Employer Total

I just used these formula just the other day in this thread:
http://www.computing.net/answers/of...

MIKE

http://www.skeptic.com/


Report •

#3
September 29, 2010 at 01:37:40
Thanks for the suggestion... my actual spreadsheet is a little more complicated. Sorry I should have specified instead of trying to simplify it. In sheet 1, I need to populate EMPLOYER TOTAL in column H and EMPLOYEE TOTAL in column I (and the column headers are different). In sheet 2, the four columns are C,D, E and F.

Oh, and FYI, I managed to populate column H using this formula... by searching for the name in sheet 1, column A and pulling the largest value in sheet 2, column E:

=MAX(INDEX(('Contribution Report'!$C$2:$C$9759='eeaseReport-2'!A8)*('Contribution Report'!$E$2:$E$9759),0))

But now I'm stuck trying to get column I, which I'm thinking could be done by looking for the next largest figure. Any thoughts?

Thanks!


Report •

Related Solutions

#4
September 29, 2010 at 04:54:25
I haven't tested anything but if you think that looking at the next largest figure will work, take a look at the LARGE function.

LARGE(array,k) where k would be 2 in your case.


Report •


Ask Question