Sheet 1

A B C AA

1 JR Jan german

2 JJ Jan american

Sheet 2

A B C AA

1 Jan Feb Mar

2

3

100 JJ

***What i need the formula in cell b1 to do is: if b1 sheet2 is equal to cell b1 in sheet1, then look for "JJ" sheet2 in the range of column A of sheet1. If the record is found then give me the value of column AA that corresponds to JJ in sheet1. 

Please help, I have been trying to get this since tuesday. Thank you very much. 

• Moved by Lanlan HuangMicrosoft contingent staff 4 hours 30 minutes ago

=INDEX(Sheet1!$AA:$AA,SUMPRODUCT((Sheet1!$A$1:$A$100="JJ")*(Sheet1!$B$1:$B$100=A$1)*ROW(Sheet1!$B$1:$B$100)))

or maybe

=INDEX(Sheet1!$AA:$AA,SUMPRODUCT((Sheet1!$A$1:$A$100="JJ")*(Sheet1!$B$1:$B$100=B$1)*ROW(Sheet1!$B$1:$B$100)))

Your description and your tables seem to be different....

Hi
This is the forum to discuss questions about Microsoft Excel develop(VBA). For your question is  about formula in Excel , I will move question to the TechNet forum for Excel

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.
Thank you for your understanding.
Best Regards,

Lan