Index Match with more than one criteria

Hello,

Can I use index / match for the following issue or should I be looking to use some other function? I've made a few attempts, but can't seem to get it to work.
Two Excel Files.

File 1- formula to be entered in this file. I need to match account number (column B of File1) with account number in File 2, plus if the value in File 2 (column Y>0) return the account number; otherwise return "No".

File 2 - account number column C, and value > 0 is in column Y.

Any help would be greatly appreciated.

TIA

Jan

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hello,

As suggested, I attached both files and remove most of the data. File 1 shows desired result. Please note file 2 has account number (customer #) in column A.

Kind regards,
Jan

Try

=IF(COUNTIFS('[File 2.xlsx]CHI'!$A:$A,B3,'[File 2.xlsx]CHI'!$Y:$Y,">0"),B3,"No")

Hi John,

Thank you so much. That formula works.

Kind regards,
Jan

Hello John,

While the formula works, it only shows the correct value when File 2 is open. If file 2 is closed, the #VALUE error shows in File 1.
I need the correct value to show when File 1 is open and File 2 is closed. Anyway around this?

Kind regards,
Jan

Hi John,

That fixed the problem.

Thank you once again for your help.

Regards,
Jan