Vlookup and If function

[Guest]

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,0)),VLOOKUP(E3,'Act
Chart'!C,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.

 

[Guest]

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

 

[Guest]

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)

 

[Dave Peterson]

You changed Toppers formula.

Change that C6 back to 1.

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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)

 

[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)?????????

 

[Guest]

I understand now what it does. I do have data on my "key" sheet all the way
through cell 200. I either get #n/a or #value!

Here is my formula.
=INDEX('Act Chart'!$D$2:$D$200,MATCH(1,('Act Chart'!$A$2:$A$200=C207)*('Act
Chart'!$C$2:$C$200=E207),0),1)

 

[Dave Peterson]

Do you have a row in 'Act Chart' that has a value in A2:A200 that matches what's
in C207 and at the same time a value in C2:C200 that matches the value in E207?

Any chance you have extra spaces in C207, E207, A2:A200, E2:E200 that makes it
not match?

And you're sure you used ctrl-shift-enter to enter the formula--you can see the
{}'s surrounding the formula in the formula bar?

====
And those numbers are real numbers--not text masquerading as numbers?

I understand now what it does. I do have data on my "key" sheet all the way
through cell 200. I either get #n/a or #value!

Here is my formula.
=INDEX('Act Chart'!$D$2:$D$200,MATCH(1,('Act Chart'!$A$2:$A$200=C207)*('Act
Chart'!$C$2:$C$200=E207),0),1)

 

[Guest]

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