Multiple Variables in an IF statement

[doctork11]

Hey everyone, I need some help on an IF function.

I'm going to use BAD pseudo-code to describe what I need...

IF E2 on wkst 1 = 3000 THEN match C2 from Wkst 1 to the corresponding value in cells F121:F236 on Wkst 2

THEN place value located (matched) from F121:F236 on Wkst 2 in F2 on Wkst 1

Of course the even tougher part is having to then match L121:236 and M121:M236 at the same time from Wkst 2 to Wkst 1

e.g. Wkst 1 Wkst 2

C2 D2 E2 F121 L121 M121
ABI 607 3000 ABI 300-511310-5002 300-131310-0000

Required Result:
Wkst 1
C2 D2 E2 F2 G2 H2
ABI 607 3000 ABI 300-511310-5002 300-131310-0000


To make it worse, I have to do this for accountants. So they need to see a completely automated script (or formula) to guarantee accuracy.

[Richard Buttrey]

Hi,

Can you upload a sample workbook, with a before and after situation so that we can see your request in context. It appears that you may need a VLOOKUP or a combination of =INDEX() and =MATCH(), but sight of a workbook will help establish the solution.

Regards

[doctork11]

Thank you Richard...

I have uploaded the spreadsheet.

[DonkeyOte]

Not sure I really understand - based on the sample the majority of combinations are not to be found.

Sheet1!F2:
=IF(COUNTIFS(Sheet2!$F$5:$F$468,$C2,Sheet2!$K$5:$K$468,$E2),$C2,"")

Sheet1!G2:
=IF($F2="","",LOOKUP(2,1/((Sheet2!$F$5:$F$468=$C2)*(Sheet2!$K$5:$K$468=$E2)),Sheet2!$L$5:$L$468))

Sheet1!H2
=IF($F2="","",LOOKUP(2,1/((Sheet2!$F$5:$F$468=$C2)*(Sheet2!$K$5:$K$468=$E2)),Sheet2!$O$5:$O$468))

(above assumes combination of Service Code and Revenue Site to be unique)

[doctork11]

This worked like a charm. Thank you for the help!