Populate cells with data from other cells if 2 matches are true

Hello,

I'm trying to auto-fill cells with data from another sheet when 2 criteria are true. If both criteria can't be met, then only the first match is true and can be filled.
Since this data is collected in 6 columns side by side, I use Iferror, if, index & match to search the column-range, match the entered value(s) and return those values if true or repeat the same formula in the next set of columns if false. The IFERROR is supposed to return the first single match if not all criteria can be met.

The formula works fine, but fills the wrong data if a match from another range can be made.

Formula:

Please Login or Register  to view this content.

I've attached a working sample and highlighted where the problem occurs with some explanation. The formula is not as complicated as it seems because the formula just repeats for every range in the sheet. Any help would be highly appreciated.Display matching names.xlsx

Thank you,

I'll have a look tonight and see if this does the trick. I hadn't thought of using helper columns before, but if I do, I can simply break it down per column. I'll use your example to accomplish this.

Dear Mr. Doverman,

I ended up going a different route which works exactly as I wanted, and without any helper columns. Because we're all here to learn and share ideas, I thought you might find it interesting to see the solution I came up with as well. I used this array formula:

Formula:

Please Login or Register  to view this content.

and added an iferror to repeat matching both cells in the next set of columns as such:

Formula:

Please Login or Register  to view this content.

What it does is this:

Lookup value from cell 1 and 2 and match in columns A and B, if both match, then return corresponding value from column C, if they don't match, then lookup value from cell 1 and 2 in columns D and E, and if both match , then return corresponding value from column F etc.

If only value from cell 1 matches and cell 2 is empty, then the first corresponding value from column A is returned.

Thanks again for your help and I hope this is of any use for future reference to you.