Look Up and Return Value

[yeshua68]

Not sure of the best formula for this situation. I'm a bit fuzzy on this.

In one spreadsheet I have an Order Number - 200022028 in column A. In column L I need a formula that looks up this order number in another spreadsheet (COR3 is the tab name) and only returns the value that in COR3 Column AN that has the world "Settlement" in COR3 column M. I hope that makes sense.

 

[Yongle]

In one spreadsheet I have an Order Number - 200022028 in column A
I need a formula that looks up this order number in another spreadsheet (COR3 is the tab name) and only returns the value that in COR3 Column AN that has the world "Settlement" in COR3 column M


Formula ssumes Order Number is in column A in sheet COR3

=INDEX('COR3'!AN:AN,MATCH(A2&"Settlement",'COR3'!A:A&'COR3'!M:M,0))