Search & Match Mulitple Criteria then Fill Cell with Data

[Swaski]

Hello Excel Guru,

Please help with this problem:

On "Log" tab find and match both EMAIL and ITEM in "Form" tab, then fill blank with Order Number.

Attached for reference. Thank you so much in advance.

[Glenn Kennedy]

In C2 copied down:
=IFERROR(INDEX(Form!$B$2:$B$5,MATCH(1,(Form!$A$2:$A$5=Log!$A2)*(Form!$C$2:$C$5=Log!B2),0)),"")

In E2, copied down:
=IFERROR(INDEX(Form!$B$2:$B$5,MATCH(1,(Form!$A$2:$A$5=Log!$A2)*(Form!$D$2:$D$5=Log!B2),0)),"")

In G2, copied down:
=IFERROR(INDEX(Form!$B$2:$B$5,MATCH(1,(Form!$A$2:$A$5=Log!$A2)*(Form!$E$2:$E$5=Log!B2),0)),"")

These are array formulae. Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[Swaski]

Hi Glen,

Thanks for the quick response. However, it only half worked. Please see attach to see if I made any mistake.

No orders were found for Amy (A3), and David suppose to have also ordered Item aaa (B5) but doesn't show.

[Glenn Kennedy]

Try this, and remove the extra space after ccc in cell Form C5.

Please Login or Register  to view this content.

[FlameRetired]

@ Glenn Kennedy,
Sharp eyes Glenn. Didn't see why my formula wasn't working.

@Swaski,

Here's another way. It's a non-array formula. Enter this in C2 and fill down. Copy that selected range and paste successively into cells E2 and G2.

Please Login or Register  to view this content.

[Swaski]

Thanks again Glenn. But I notice this formula would only work if the emails were in the same sequence on both sheet. If they do not match the same number cell then this formula won't work, is that true?

[Swaski]

@ FlameRetired
With this formula, amy is completely dismissed. Peter did not order item ccc but still got counted for.

EDIT: sorry i opened the wrong file. FlameRetired formula works perfectly! THANK YOU!!!