Index Match Match

[Robby19]

Trying to get a unique number (Tracking Number) to show when 2 items are matched (EDIPI and Pending Status). Currently I am using

=INDEX(TFRS_FTAP[TRACKINGNUMBER],MATCH([@EDIPI],TFRS_FTAP[EDIPI],0),MATCH("pending*",TFRS_FTAP[STATUS],0))

However, I only receive a #REF error when it computes. Can you help?

 

[iliace]

It looks like you are trying to use multiple row criteria for INDEX. It doesn't work that way.

The third parameter for INDEX should return a column number. Because you are using (what looks like) a single column for the TRACKINGNUMBER, the last argument is not applicable, since it will have to be 1.

Maybe try something like this (off top of my head):

=INDEX(TFRS_FTAP[TRACKINGNUMBER], MATCH(1, ([@EDIPI]=TFRS_FTAP[EDIPI]) * (LEFT(TFRS_FTAP[STATUS], 7)="pending"), 0)

You would have to use Ctrl+Shift+Enter to confirm the formula (instead of just Enter by itself), because this is an array formula. Also note that this will only return the first match, in case multiple matching EDIPIs have a "pending" status.

 

[Robby19]

Worked exactly as intended. Thank you so much!