Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Index/Match ?

0 views
Skip to first unread message

carl

unread,
Dec 23, 2008, 6:46:12 PM12/23/08
to
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.


@consumerdotorg Bernie Deitrick

unread,
Dec 23, 2008, 7:26:40 PM12/23/08
to
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

"carl" <ca...@discussions.microsoft.com> wrote in message
news:A5C0D8D7-9EC7-4881...@microsoft.com...

T. Valko

unread,
Dec 23, 2008, 7:53:03 PM12/23/08
to
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)

--
Biff
Microsoft Excel MVP


"carl" <ca...@discussions.microsoft.com> wrote in message
news:A5C0D8D7-9EC7-4881...@microsoft.com...

0 new messages