Conditional Formula Referencing Multiple Cells on Different Worksheets

[tigeranddolly]

Hi there,

I am pretty rusty with my Excel skills, so I am hoping I can get some help. I have a Workbook that has two tabs: Cheques and Invoice Approvals. In the Cheques worksheet I have cheques listed with sometime a few invoice numbers associated with that cheque in one column, separated by commas. In the Invoice approval worksheet I have each invoice number in a separate row. What I would like is to have a column in the Invoice Approval worksheet that references the Cheques tab that could check for a matching invoice number in each worksheet and fill in the cheque number on the Invoice Approval sheet. What sort of formula should I use?

Thank you in advance! Please let me know if I need to clarify anything.

[JBeaucaire]

Probably a cleverly formatted INDEX/MATCH.

Attach a sample workbook with just enough sample data to demonstrate all of this. 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.

[tigeranddolly]

Thanks for replying! Here is a sample workbook with fake data.

[JBeaucaire]

I've created some named ranges to make the formula a little easier to read:

=IFERROR(IF($C2="", INDEX(CheckNumbers, MATCH($B2&"-"&$D2, CompanyAmount, 0)), INDEX(CheckNumbers, MATCH("*"&$C2&"*", Invoices,0))), "")