Formula Required to Lookup and Match Multiple Criteria

[The_Snook]

Hi,

I've got a schedule of orders and subsequent invoices raised and would like to establish which invoices had original related orders.

I've attached an example of what i'm working with and included some additional narrative in comments boxes. I've attempted the first formula myself which does the job but i'm not sure it's the most efficient? i'd be grateful if someone could point out an alternative/better solution?

If you have any queries just give me a shout.

Thanks in advance,

Snook

[ragulduy]

I don't see anything wrong with how you did it. I guess one alternative would be to use an array formula, such as:
=IF(SUM(IF(B3&C3&D3=Orders!B3:B20&Orders!C3:C20&Orders!D3:D20,1,0))>0,"True","False").

I'd guess the quickest calculation wise would probably to have helper columns with the concatenated values of columns B, C and D and use a countif or something with the helper columns.

[RobertMika]

F3:
=COUNTIFS(Orders!$B$3:$B$20,Invoice!B3,Orders!$C$3:$C$20,Invoice!C3,Orders!$D$3:$D$20,D3)>0
H3
(confirm Contrl+shift+enter
=IFERROR(MATCH(1,(Orders!$B$3:$B$20=Invoice!B3)*(Orders!$C$3:$C$20=Invoice!C3)*(Orders!$D$3:$D$20=D3),0),"")
J3
=COUNTIFS(Orders!$B$3:$B$20,Invoice!B3,Orders!$C$3:$C$20,Invoice!C3,Orders!$D$3:$D$20,D3)

[alvin-chung]

Hi, try these formulas

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

and copy the formulas down...

ps: first two of the above are array formulas, i.e. press ctrl+shift+enter instead of just enter



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin