Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
can't figure out LOOKUP with 2 variables
I think this a HLOOKUP or INDEX problem for some pension numbers. I have 2 worksheets in different workbooks, both have employee names and amounts deducted by the employee or amounts contributed by employer. Not everybody has the same types of contributions; Pretax, Employer match, after tax and safe harbor amounts. One worksheet from the pension vendor lists the employee name in a column) and lists the employee name and dollar amount separately each time the employee contribution or EMPLOYER contribution appears. The other worksheet lists only the employee name and then the amounts across rows, based on what is deducted by the employee or contributed by the employer. Describing the worksheets: the vendor worksheet has LN, FN, Type of contribution (Pretax, After tax, match, and safe harbor - not necessarily in that order by employee), and the Amount is listed in columns by LN Ascending. The employer worksheet has a header of:LN, FN, and category (Employee After tax, Pretax, Match, and safe harbor), Can you suggest a furmula that will allow me to check both employee name and the contribution amounts for even matching? I'm trying to find all variables between these lists. I tried VLOOKUP matching separate types of contribtions sorted employee name ascending, but I'm really looking for one formula that allows the complete worksheets to be compared with each other without doing a lot of cutting & pasting of info. Any help is greatly appreciated. Thanks. Mark Boston, MA -- LTUser54 ------------------------------------------------------------------------ LTUser54's Profile: http://www.excelforum.com/member.php...o&userid=33459 View this thread: http://www.excelforum.com/showthread...hreadid=546765 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
can't figure out LOOKUP with 2 variables
If I have understod the problem correctly, a simple solution is to add an extra column to both sheets which contains the concatenated values of the two columns that you are interested in. e.g. set C1 as = A1 & B1 You can then use VLOOKUP on this columns to look for matches which have both the original values in common. -- mrice Research Scientist with many years of spreadsheet development experience ------------------------------------------------------------------------ mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931 View this thread: http://www.excelforum.com/showthread...hreadid=546765 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Bubble Charts with two non-numerical variables | Excel Worksheet Functions | |||
multi sheet lookup with multiple results | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |