Combining an AND and IF function - how to?

[newtoexcel24]

Hi,
I am trying to combine an AND and IF functions to be able to insert the corresponding account number in cell C5.
I am attaching the file for your reference. Can anyone help me do that please? Thank you,
Newuser

[tigeravatar]

newtoexcel24,

Welcome to the forum!
Using your posted example workbook, you can use this formula in cell C5:

=INDEX(G1:G13,MATCH(1,INDEX((E1:E13=C3)*(F1:F13=C4),),0))

[newtoexcel24]

Hi thanks for your reply. Actually the formulae works with that combination only; however if you either change the bank or currency, it no longer works.
In cells C3 and C4 (there is a data validation in those cells) we can choose the bank and the currency, and depending on those two information, the account number will change. Do you think you can help?
Thank you
new user

[tigeravatar]

I tested and verified it still works if you change those cells. Just make sure that you change them to a valid combination. For example, there is no row with both "state" and "yen" so that would result in an error. Just like there is no row for "mcb" and "euro", so that combination will also result in an error. But if you change it to "bank one" and "suisse francs", which is a valid combination, it pulls the correct result, 67898.

[jeffreybrown]

You are probably picking two values from C3 and C4 that do not go together.

Example:
From your drop down I can pick state and yen, there is no match for that combination. To prevent this...

=IFERROR(INDEX(G1:G13,MATCH(1,INDEX((E1:E13=C3)*(F1:F13=C4),),0)),"")

[newtoexcel24]

Right! Super Thank you very much! This is great!!!