If Function

[sgrech]

Hi,
Can somebody please help with this?

I have a user who is populating a fund name in cell A1 (e.g. LF1) and the currency of that fund in B1 (e.g. GBP).

Cell C1 needs to extract the account code (from a separate wks) based on the inputs previously.

On a account information wks there are many different funds, but what complicates things (at least for me) is that a single can have multiple currencies so in our example LF1 might also have account numbers for USD, EUR as well as GBP. How can I find the correct account code and deposit it in C1. I am assuming I can't use nested IF's are there are too many variables.

Regards
Simon

[Cutter]

I'm assuming that the data on the other sheet is set up with the Fund Code in Column A, Currency Code in Column B and the Account Code in Column C

Use the sumproduct function: In C1 type the following
=SUMPRODUCT(--(sheet2!A1:A1000=A1),--(sheet2!B1:B1000=B1),sheet2!C1:C1000)

[Domenic]

If your 'Account Code' is made up of alphanumerical characters, try the following...

=INDEX(AccountCodeRange,MATCH(1,(FundRange=FundName)*(CurrencyRange=Currency),0))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

Hi,
Can somebody please help with this?

I have a user who is populating a fund name in cell A1 (e.g. LF1) and the currency of that fund in B1 (e.g. GBP).

Cell C1 needs to extract the account code (from a separate wks) based on the inputs previously.

On a account information wks there are many different funds, but what complicates things (at least for me) is that a single can have multiple currencies so in our example LF1 might also have account numbers for USD, EUR as well as GBP. How can I find the correct account code and deposit it in C1. I am assuming I can't use nested IF's are there are too many variables.

Regards
Simon

[sgrech]

I'm assuming that the data on the other sheet is set up with the Fund Code in Column A, Currency Code in Column B and the Account Code in Column C

Use the sumproduct function: In C1 type the following
=SUMPRODUCT(--(sheet2!A1:A1000=A1),--(sheet2!B1:B1000=B1),sheet2!C1:C1000)

Thanks CUTTER that's works treat. IF the account number was in a column say H how would I amend the formula.

Also I would be most grateful if you explain the code more specifically the "--" bit.

Thanks
Simon

[Domenic]

...IF the account number was in a column say H how would I amend the formula.

=SUMPRODUCT(--(sheet2!A1:A1000=A1),--(sheet2!B1:B1000=B1),sheet2!H1:H1000)

Also I would be most grateful if you explain the code more specifically the "--" bit.

Have a look at Aladin's contribution here...

http://www.mrexcel.com/board2/viewtopic.php?t=103530

Hope this helps!

[Cutter]

You're quite welcome, Simon. Thanks for the feedback.

I see Domenic has answered your question about Column H.

For an explanation about the double unary try this site:
http://www.mcgimpsey.com/excel/formulae/doubleneg.html