Index/Match ?

[carl]

I have a table like this:

ID Code Notes
BOX549 a2 Drop
BOX355 aA Drop
BOX355 AA Add

I am trying to find a formula for column "Notes" that will produce this
result:

Code ID Notes
a2 BOX549 Drop
A3 BOX355
AA BOX355 Add
aA BOX355 Drop
Aa BOX355


Thank you in advance and Happy Holidays.

 

[Bernie Deitrick]

Carl,

Assuming you have your table on Sheet1, A1:C4, then use, and your other code
and ID values in columns A and B, starting in row 2, then use this in cell
C2

=IF(SUMPRODUCT((EXACT(A2,Sheet1!$B$2:$B$4))*(EXACT(B2,Sheet1!$A$2:$A$4))*ROW(Sheet1!$A$2:$A$4))=0,"",INDEX(Sheet1!C:C,SUMPRODUCT((EXACT(A2,Sheet1!$B$2:$B$4))*(EXACT(B2,Sheet1!$A$2:$A$4))*ROW(Sheet1!$A$2:$A$4))))

HTH,
Bernie
MS Excel MVP

 

[T. Valko]

Maybe this array formula** :

Table in the range H2:J4.

Lookup_values starting in A2:B2

=IF(SUM((ISNUMBER(FIND(A2,I$2:I$4)))*(H$2:H$4=B2)),INDEX(J$2:J$4,MATCH(1,(ISNUMBER(FIND(A2,I$2:I$4)))*(H$2:H$4=B2),0)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)