IF & COUNTIFS Formula?

[banneduser123]

Hi, I'm having trouble working out the below....hoping someone can help.

I am working in Two Sheets.

Trying to get a formula that does this:

Looks for the value of Sheet1 K2, in Sheet2 column B.
If that exists, then it looks to see if the balance in Sheet1 J2 is greater or less than the value of the respective row in Sheet2 column C.
If Sheet1 J2 is greater than the respective value, then it returns the value from column E. If less than, then it returns the value from column F.

please let me know if that wasn't clear, or if you need more info.

appreciate any help

 

[etaf]

TRY
=IF(J2>INDEX(Sheet2!C:C,MATCH(Sheet1!K2,Sheet2!B:B,0)),INDEX(Sheet2!E:E,MATCH(Sheet1!K2,Sheet2!B:B,0)),INDEX(Sheet2!F:F,MATCH(Sheet1!K2,Sheet2!B:B,0)))

What happens if J2 = Sheet2!C:C

 

[banneduser123]

Ah, perfect. Thanks so much for the help.
To answer your question, i amended the formula to be:

=IF(J2>=INDEX.....

 

[etaf]

you are welcome