SOLVED

Excel formula help needed

Copper Contributor

Hello team,

i have an excel file with 2 sheets. Sheet 1 is what i ordered, and sheet 2 is what i received.

so i am trying to do the following:

In sheet 1 column N, i am trying to get the date of the item that matches criteria 1 and 2 from sheet 2 when received.

So when item in Column M in sheet 1, example R000025081, and column E in sheet 1, example 1002740521, when they match same in sheet 2, to return column D receiving date, if possible if the receiving date is older that date in the ordering sheet 1,

Any help? i manage to get return cell for date, but only when i match 1 cell.

any help is appreciated

5 Replies

@Ljube86 

 

Is it possible for you to post the actual spreadsheet? I think this is easily resolved, but it's a lot easier to demonstrate it with your actual sheet, so as to not have to create my own replica based on your image.

@mathetes 

Thank you for looking into this.

best response confirmed by Ljube86 (Copper Contributor)
Solution

@Ljube86 

As variant

=IF(
      IFNA(INDEX(Received!$D$2:$D$10,MATCH(C2&A2,Received!$A$2:$A$10&Received!$B$2:$B$10,0)),0)>B2,
      IFNA(INDEX(Received!$D$2:$D$10,MATCH(C2&A2,Received!$A$2:$A$10&Received!$B$2:$B$10,0)),""),
"")

Thank you gents. 

@Ljube86 , you are welcome

1 best response

Accepted Solutions
best response confirmed by Ljube86 (Copper Contributor)
Solution

@Ljube86 

As variant

=IF(
      IFNA(INDEX(Received!$D$2:$D$10,MATCH(C2&A2,Received!$A$2:$A$10&Received!$B$2:$B$10,0)),0)>B2,
      IFNA(INDEX(Received!$D$2:$D$10,MATCH(C2&A2,Received!$A$2:$A$10&Received!$B$2:$B$10,0)),""),
"")

View solution in original post