Complicated Formula Needed

[midgetmogalle]

I am trying to create a formula that pulls info from one sheet to another. The first sheet has the following info:


Position ID (A), Status (B), Payroll Name (C), Deduction Code (D), 401k Deduction Amount (E)
The second sheet has the following info:
Match (A), 401k Deduction Amount (B), Match 2 (C), Contribution (D), Payroll Name (E), Status (F)

I need 401k Deduction Amount brought into the first worksheet from the second and match it with the Payroll Name.

I would also like to create an "IF" formula (I think) that would auto-fill to "81" if a #% is entered, and "opt-out" if "opt-out" is entered.

Please help!!!

[Pecoflyer]

Hi
please post a sample sheet ( no pics please)

[midgetmogalle]

Sheet 1:Position IDStatusPayroll NameDeduction Code401k Deduction Amount
D17000110ActiveGalle, Monique R815%
Sheet 2:
Match401k Deduction AmountMatch 2 ContributionPayroll NameStatus3%3%3.03.0Aker,YvetteAuto Enrollment

[Pecoflyer]

It would be easier for us to see your data.
You can find explanations on how to attach a sheet at https://www.msofficeforums.com/faq.p...b3_attachments

[midgetmogalle]

Here you go...

[Pecoflyer]

Like this ? ( adapt the ranges to your need)

[midgetmogalle]

Perfect. And what about the Deduction Code auto-fill?

[midgetmogalle]

That didn't work

[Pecoflyer]

Can't seem to find that on sheet 2?

[midgetmogalle]

Here is the actual spreadsheet. Maybe this will help.

[Pecoflyer]

Could you please fill in an example of what a result would be and on which basis ?
Except for 401k Deduction amount and payroll names there are no common column headers?

[midgetmogalle]

I know. Two different people created each of these spreadsheets. I am hoping since the Payroll Name and 401k Deduction Amount is really the only info I will be looking at, I can get a formula for those two columns.

You were on the right path with Jerry Bently, the formula just didn't work once I copied and pasted it into my spreadsheet.

I want to be able to take Column B from the second worksheet and have it auto-fill into column E of the first worksheet depending on the Payroll Name.

Then, I want Column D to auto-fill either "81" or "opt-out" depending on what is in column E on the first worksheet.

[Pecoflyer]

The attached adapted as per your requirements. Be aware the name formatting is not the same in both sheets, on the first one there is a space between the comma and the rest. ( hence the use of the SUBSTITUTE function)
I left everything blank if no name was found

[midgetmogalle]

Thank you. This appears to have worked great!

[Pecoflyer]

Perhaps indicate your thread as " solved"? ( under "thread tools")