I have two sheets. One is where I want the data to be, while the other is my
key.

On my "key" sheet, I have four columns of data as such: dept, dept name,
activity, activity name. But for each department, there could be 2-6
different activities for each one. I have it set up like the following:
a b c d
100 admin 100 general
100 admin 101 HR
100 admin 102 IT

On my data sheet, I have a number of columns but am trying to bring back the
activity name to the data sheet set up like so(dept, dept name, activity,
activity name). The dept # and activity #'s are the data sheet. I have no
trouble with the dept name since I used a vlookup formula. My trouble comes
to the activity name. I tried to use
=IF(ISNUMBER(MATCH(C3,'Act Chart'!A:B,0)),VLOOKUP(C3,'Act
Chart'!A:B,1,0),IF(ISNUMBER(MATCH(E3,'Act Chart'!C:D,0)),VLOOKUP(E3,'Act
Chart'!C:D,4,0),""))

I was thinking it would match/lookup the department in column A (from the
Key) and bring back the activity name to my data sheet. It isn't working.

Try this (modified to suit your ranges) , entered with Ctrl+Shift+Enter

=INDEX($D$2:$D$10,MATCH(1,($A$2:$A$10="Dept")*($C$ 2:$C$10="Activity"),0),1)

It matches Dept & Activity and returns activity name (from column D)

HTH


"michelle" wrote:

I have two sheets. One is where I want the data to be, while the other is my
key.

On my "key" sheet, I have four columns of data as such: dept, dept name,
activity, activity name. But for each department, there could be 2-6
different activities for each one. I have it set up like the following:
a b c d
100 admin 100 general
100 admin 101 HR
100 admin 102 IT

On my data sheet, I have a number of columns but am trying to bring back the
activity name to the data sheet set up like so(dept, dept name, activity,
activity name). The dept # and activity #'s are the data sheet. I have no
trouble with the dept name since I used a vlookup formula. My trouble comes
to the activity name. I tried to use
=IF(ISNUMBER(MATCH(C3,'Act Chart'!A:B,0)),VLOOKUP(C3,'Act
Chart'!A:B,1,0),IF(ISNUMBER(MATCH(E3,'Act Chart'!C:D,0)),VLOOKUP(E3,'Act
Chart'!C:D,4,0),""))

I was thinking it would match/lookup the department in column A (from the
Key) and bring back the activity name to my data sheet. It isn't working.

I tried this, but I get a +n/a. I substituted for my ranges.

=INDEX('Act Chart'!$D$2:$D$200,MATCH(C6,('Act
Chart'!$A$2:$A$200="Dept")*('Act Chart'!$C$2:$C$200="Activity"),0),1)

"Toppers" wrote:

Try this (modified to suit your ranges) , entered with Ctrl+Shift+Enter

=INDEX($D$2:$D$10,MATCH(1,($A$2:$A$10="Dept")*($C$ 2:$C$10="Activity"),0),1)

It matches Dept & Activity and returns activity name (from column D)

HTH


"michelle" wrote:

I have two sheets. One is where I want the data to be, while the other is my
key.

On my "key" sheet, I have four columns of data as such: dept, dept name,
activity, activity name. But for each department, there could be 2-6
different activities for each one. I have it set up like the following:
a b c d
100 admin 100 general
100 admin 101 HR
100 admin 102 IT

On my data sheet, I have a number of columns but am trying to bring back the
activity name to the data sheet set up like so(dept, dept name, activity,
activity name). The dept # and activity #'s are the data sheet. I have no
trouble with the dept name since I used a vlookup formula. My trouble comes
to the activity name. I tried to use
=IF(ISNUMBER(MATCH(C3,'Act Chart'!A:B,0)),VLOOKUP(C3,'Act
Chart'!A:B,1,0),IF(ISNUMBER(MATCH(E3,'Act Chart'!C:D,0)),VLOOKUP(E3,'Act
Chart'!C:D,4,0),""))

I was thinking it would match/lookup the department in column A (from the
Key) and bring back the activity name to my data sheet. It isn't working.

You changed Toppers formula.

Change that C6 back to 1.

Don't forget to do this: entered with Ctrl+Shift+Enter


michelle wrote:

I tried this, but I get a +n/a. I substituted for my ranges.

=INDEX('Act Chart'!$D$2:$D$200,MATCH(C6,('Act
Chart'!$A$2:$A$200="Dept")*('Act Chart'!$C$2:$C$200="Activity"),0),1)

"Toppers" wrote:

Try this (modified to suit your ranges) , entered with Ctrl+Shift+Enter

=INDEX($D$2:$D$10,MATCH(1,($A$2:$A$10="Dept")*($C$ 2:$C$10="Activity"),0),1)

It matches Dept & Activity and returns activity name (from column D)

HTH


"michelle" wrote:

I have two sheets. One is where I want the data to be, while the other is my
key.

On my "key" sheet, I have four columns of data as such: dept, dept name,
activity, activity name. But for each department, there could be 2-6
different activities for each one. I have it set up like the following:
a b c d
100 admin 100 general
100 admin 101 HR
100 admin 102 IT

On my data sheet, I have a number of columns but am trying to bring back the
activity name to the data sheet set up like so(dept, dept name, activity,
activity name). The dept # and activity #'s are the data sheet. I have no
trouble with the dept name since I used a vlookup formula. My trouble comes
to the activity name. I tried to use
=IF(ISNUMBER(MATCH(C3,'Act Chart'!A:B,0)),VLOOKUP(C3,'Act
Chart'!A:B,1,0),IF(ISNUMBER(MATCH(E3,'Act Chart'!C:D,0)),VLOOKUP(E3,'Act
Chart'!C:D,4,0),""))

I was thinking it would match/lookup the department in column A (from the
Key) and bring back the activity name to my data sheet. It isn't working.

--

Dave Peterson

I had to change the formula to match my referenced cells. When I had the 1 vs
the C6, I still get the #n/a. What does the Ctrl+Shift+Enter do? I tried
that too several times and it didn't do anything?

I evaluated the formula and keeps coming to the very first portion of the
Match formula with a #n/a.

"Dave Peterson" wrote:

You changed Toppers formula.

Change that C6 back to 1.

Don't forget to do this: entered with Ctrl+Shift+Enter


michelle wrote:

I tried this, but I get a +n/a. I substituted for my ranges.

=INDEX('Act Chart'!$D$2:$D$200,MATCH(C6,('Act
Chart'!$A$2:$A$200="Dept")*('Act Chart'!$C$2:$C$200="Activity"),0),1)

"Toppers" wrote:

Try this (modified to suit your ranges) , entered with Ctrl+Shift+Enter

=INDEX($D$2:$D$10,MATCH(1,($A$2:$A$10="Dept")*($C$ 2:$C$10="Activity"),0),1)

It matches Dept & Activity and returns activity name (from column D)

HTH


"michelle" wrote:

I have two sheets. One is where I want the data to be, while the other is my
key.

On my "key" sheet, I have four columns of data as such: dept, dept name,
activity, activity name. But for each department, there could be 2-6
different activities for each one. I have it set up like the following:
a b c d
100 admin 100 general
100 admin 101 HR
100 admin 102 IT

On my data sheet, I have a number of columns but am trying to bring back the
activity name to the data sheet set up like so(dept, dept name, activity,
activity name). The dept # and activity #'s are the data sheet. I have no
trouble with the dept name since I used a vlookup formula. My trouble comes
to the activity name. I tried to use
=IF(ISNUMBER(MATCH(C3,'Act Chart'!A:B,0)),VLOOKUP(C3,'Act
Chart'!A:B,1,0),IF(ISNUMBER(MATCH(E3,'Act Chart'!C:D,0)),VLOOKUP(E3,'Act
Chart'!C:D,4,0),""))

I was thinking it would match/lookup the department in column A (from the
Key) and bring back the activity name to my data sheet. It isn't working.

--

Dave Peterson

When you have something like this:
('Act Chart'!$C$2:$C$200="Activity")
in your formula, it'll result in 199 true/falses.

When you multiply them by
('Act Chart'!$A$2:$A$200="Dept")
(which is also 199 true/falses)

You get 199 1's and 0's. (true*true =1, anything else is 0)

the match(1,(....),0)
says to return the first row where both of those things happen to be true
(true*true=1).

You tell excel that this is an array formula by using ctrl-shift-enter. Then
excel knows that it has to essentially do a bunch of loops (a2 with c2, a3 with
c3, a4 with c4, ...).

If you do it correctly, excel will wrap your formula with {}'s. Don't type them
yourself.

If you get n/a, maybe you don't have any rows in 2:199 that have Dept and
activity in them (on that same row)?????????

michelle wrote:

I had to change the formula to match my referenced cells. When I had the 1 vs
the C6, I still get the #n/a. What does the Ctrl+Shift+Enter do? I tried
that too several times and it didn't do anything?

I evaluated the formula and keeps coming to the very first portion of the
Match formula with a #n/a.

"Dave Peterson" wrote:

You changed Toppers formula.

Change that C6 back to 1.

Don't forget to do this: entered with Ctrl+Shift+Enter


michelle wrote:

I tried this, but I get a +n/a. I substituted for my ranges.

=INDEX('Act Chart'!$D$2:$D$200,MATCH(C6,('Act
Chart'!$A$2:$A$200="Dept")*('Act Chart'!$C$2:$C$200="Activity"),0),1)

"Toppers" wrote:

Try this (modified to suit your ranges) , entered with Ctrl+Shift+Enter

=INDEX($D$2:$D$10,MATCH(1,($A$2:$A$10="Dept")*($C$ 2:$C$10="Activity"),0),1)

It matches Dept & Activity and returns activity name (from column D)

HTH


"michelle" wrote:

I have two sheets. One is where I want the data to be, while the other is my
key.

On my "key" sheet, I have four columns of data as such: dept, dept name,
activity, activity name. But for each department, there could be 2-6
different activities for each one. I have it set up like the following:
a b c d
100 admin 100 general
100 admin 101 HR
100 admin 102 IT

On my data sheet, I have a number of columns but am trying to bring back the
activity name to the data sheet set up like so(dept, dept name, activity,
activity name). The dept # and activity #'s are the data sheet. I have no
trouble with the dept name since I used a vlookup formula. My trouble comes
to the activity name. I tried to use
=IF(ISNUMBER(MATCH(C3,'Act Chart'!A:B,0)),VLOOKUP(C3,'Act
Chart'!A:B,1,0),IF(ISNUMBER(MATCH(E3,'Act Chart'!C:D,0)),VLOOKUP(E3,'Act
Chart'!C:D,4,0),""))

I was thinking it would match/lookup the department in column A (from the
Key) and bring back the activity name to my data sheet. It isn't working.

--

Dave Peterson


--

Dave Peterson

Further to Dave's reply:

You need to replace "Dept" & "Activity" by the cell adresses which contain
the Dept and Activity you are trying to match (C3 & E3 ???).

HTH


"michelle" wrote:

I tried this, but I get a +n/a. I substituted for my ranges.

=INDEX('Act Chart'!$D$2:$D$200,MATCH(C6,('Act
Chart'!$A$2:$A$200="Dept")*('Act Chart'!$C$2:$C$200="Activity"),0),1)

"Toppers" wrote:

Try this (modified to suit your ranges) , entered with Ctrl+Shift+Enter

=INDEX($D$2:$D$10,MATCH(1,($A$2:$A$10="Dept")*($C$ 2:$C$10="Activity"),0),1)

It matches Dept & Activity and returns activity name (from column D)

HTH


"michelle" wrote:

I have two sheets. One is where I want the data to be, while the other is my
key.

On my "key" sheet, I have four columns of data as such: dept, dept name,
activity, activity name. But for each department, there could be 2-6
different activities for each one. I have it set up like the following:
a b c d
100 admin 100 general
100 admin 101 HR
100 admin 102 IT

On my data sheet, I have a number of columns but am trying to bring back the
activity name to the data sheet set up like so(dept, dept name, activity,
activity name). The dept # and activity #'s are the data sheet. I have no
trouble with the dept name since I used a vlookup formula. My trouble comes
to the activity name. I tried to use
=IF(ISNUMBER(MATCH(C3,'Act Chart'!A:B,0)),VLOOKUP(C3,'Act
Chart'!A:B,1,0),IF(ISNUMBER(MATCH(E3,'Act Chart'!C:D,0)),VLOOKUP(E3,'Act
Chart'!C:D,4,0),""))

I was thinking it would match/lookup the department in column A (from the
Key) and bring back the activity name to my data sheet. It isn't working.

I did that too. What am I doing wrong? What does the ctrl+shift+enter do?
I tried that and it doesn't do anything? Here is my formula.

=INDEX('Act Chart'!$D$2:$D$200,MATCH(1,('Act Chart'!$A$2:$A$200=C14)*('Act
Chart'!$C$2:$C$200=E14),0),1)

"Toppers" wrote:

Further to Dave's reply:

You need to replace "Dept" & "Activity" by the cell adresses which contain
the Dept and Activity you are trying to match (C3 & E3 ???).

HTH


"michelle" wrote:

I tried this, but I get a +n/a. I substituted for my ranges.

=INDEX('Act Chart'!$D$2:$D$200,MATCH(C6,('Act
Chart'!$A$2:$A$200="Dept")*('Act Chart'!$C$2:$C$200="Activity"),0),1)

"Toppers" wrote:

Try this (modified to suit your ranges) , entered with Ctrl+Shift+Enter

=INDEX($D$2:$D$10,MATCH(1,($A$2:$A$10="Dept")*($C$ 2:$C$10="Activity"),0),1)

It matches Dept & Activity and returns activity name (from column D)

HTH


"michelle" wrote:

I have two sheets. One is where I want the data to be, while the other is my
key.

On my "key" sheet, I have four columns of data as such: dept, dept name,
activity, activity name. But for each department, there could be 2-6
different activities for each one. I have it set up like the following:
a b c d
100 admin 100 general
100 admin 101 HR
100 admin 102 IT

On my data sheet, I have a number of columns but am trying to bring back the
activity name to the data sheet set up like so(dept, dept name, activity,
activity name). The dept # and activity #'s are the data sheet. I have no
trouble with the dept name since I used a vlookup formula. My trouble comes
to the activity name. I tried to use
=IF(ISNUMBER(MATCH(C3,'Act Chart'!A:B,0)),VLOOKUP(C3,'Act
Chart'!A:B,1,0),IF(ISNUMBER(MATCH(E3,'Act Chart'!C:D,0)),VLOOKUP(E3,'Act
Chart'!C:D,4,0),""))

I was thinking it would match/lookup the department in column A (from the
Key) and bring back the activity name to my data sheet. It isn't working.

Thanks for all the help Toppers and Dave. It worked finally. Thank you.

"michelle" wrote:

I have two sheets. One is where I want the data to be, while the other is my
key.

On my "key" sheet, I have four columns of data as such: dept, dept name,
activity, activity name. But for each department, there could be 2-6
different activities for each one. I have it set up like the following:
a b c d
100 admin 100 general
100 admin 101 HR
100 admin 102 IT

On my data sheet, I have a number of columns but am trying to bring back the
activity name to the data sheet set up like so(dept, dept name, activity,
activity name). The dept # and activity #'s are the data sheet. I have no
trouble with the dept name since I used a vlookup formula. My trouble comes
to the activity name. I tried to use
=IF(ISNUMBER(MATCH(C3,'Act Chart'!A:B,0)),VLOOKUP(C3,'Act
Chart'!A:B,1,0),IF(ISNUMBER(MATCH(E3,'Act Chart'!C:D,0)),VLOOKUP(E3,'Act
Chart'!C:D,4,0),""))

I was thinking it would match/lookup the department in column A (from the
Key) and bring back the activity name to my data sheet. It isn't working.