|
Vlookup and If function
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. |
Vlookup and If function
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. |
Vlookup and If function
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. |
Vlookup and If function
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 |
Vlookup and If function
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 |
Vlookup and If function
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. |
Vlookup and If function
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. |
Vlookup and If function
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 |
Vlookup and If function
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" wrote: 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 |
Vlookup and If function
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? michelle wrote: 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" wrote: 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 -- Dave Peterson |
Vlookup and If function
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. |
| All times are GMT +1. The time now is 05:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com