# VLOOKUP + IF formula???

Microsoft Microsoft excel 2007 (pc)
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 TotalDoe, JaneThe 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/2010Doe, Jane EMPLOYEE PRE-TAX 96.15 05/20/2010Doe, Jane EMPLOYEE PRE-TAX 96.15 06/03/2010Doe, Jane EMPLOYEE PRE-TAX 96.15 06/17/2010Doe, Jane EMPLOYEE TOTAL 384.60Doe, Jane EMPLOYER MATCH 307.69 05/06/2010Doe, Jane EMPLOYER MATCH 307.69 05/20/2010Doe, Jane EMPLOYER MATCH 307.69 06/03/2010Doe, Jane EMPLOYER MATCH 307.69 06/17/2010Doe, Jane EMPLOYER TOTAL 1,230.76So, 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???

#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 TotalI just used these formula just the other day in this thread:http://www.computing.net/answers/of...MIKEhttp://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 •