Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
I am trying to build a worksheet with 50 tabs and a summary page. Each
contain lists of data. I want the summary page to look up and match the data on each sheet using employee names. I have been using the VLOOKUP formula, but am having troubles: I have 20 or so rows of data on the summary page. However, not every name appears on every worksheet. So if the name is not there, it returns #VALUE!, which makes it so my summing formula does not work. So I have also been trying to use an IF formula. Additionally, I can get one first name to work so long as it is also the first name on the other tab, but name 2 does not work - even when it is also name 2 on the other tabs. Here is what my VLOOKUP formula looks like: VLOOKUP($A$7:$A$33,'Event 1'!$A$17:$G$67,7,FALSE) And my IF statement is this: =IF(VLOOKUP($A$7:$A$33,'Event 1'!$A$17:$G$67,7,FALSE)0.01,VLOOKUP($A$7:$A$33,'E vent 1'!$A$17:$G$17,7,FALSE),0) The VLOOKUP works (with the constraints above) but if the value is False, it does not return 0, it retruns #VALUE! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
Try the iserror function, if it returns an error insert "" else vlookup
=IF(ISERROR(VLOOKUP($A$7:$A$33,'Event 1'!$A$17:$G$67,7,FALSE)),"",VLOOKUP($A$7:$A$33,'Ev ent 1'!$A$17:$G$67,7,FALSE)) -- -John Please rate when your question is answered to help us and others know what is helpful. "a" wrote: I am trying to build a worksheet with 50 tabs and a summary page. Each contain lists of data. I want the summary page to look up and match the data on each sheet using employee names. I have been using the VLOOKUP formula, but am having troubles: I have 20 or so rows of data on the summary page. However, not every name appears on every worksheet. So if the name is not there, it returns #VALUE!, which makes it so my summing formula does not work. So I have also been trying to use an IF formula. Additionally, I can get one first name to work so long as it is also the first name on the other tab, but name 2 does not work - even when it is also name 2 on the other tabs. Here is what my VLOOKUP formula looks like: VLOOKUP($A$7:$A$33,'Event 1'!$A$17:$G$67,7,FALSE) And my IF statement is this: =IF(VLOOKUP($A$7:$A$33,'Event 1'!$A$17:$G$67,7,FALSE)0.01,VLOOKUP($A$7:$A$33,'E vent 1'!$A$17:$G$17,7,FALSE),0) The VLOOKUP works (with the constraints above) but if the value is False, it does not return 0, it retruns #VALUE! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
I have tried that and it still reads #VALUE!
"John Bundy" wrote: Try the iserror function, if it returns an error insert "" else vlookup =IF(ISERROR(VLOOKUP($A$7:$A$33,'Event 1'!$A$17:$G$67,7,FALSE)),"",VLOOKUP($A$7:$A$33,'Ev ent 1'!$A$17:$G$67,7,FALSE)) -- -John Please rate when your question is answered to help us and others know what is helpful. "a" wrote: I am trying to build a worksheet with 50 tabs and a summary page. Each contain lists of data. I want the summary page to look up and match the data on each sheet using employee names. I have been using the VLOOKUP formula, but am having troubles: I have 20 or so rows of data on the summary page. However, not every name appears on every worksheet. So if the name is not there, it returns #VALUE!, which makes it so my summing formula does not work. So I have also been trying to use an IF formula. Additionally, I can get one first name to work so long as it is also the first name on the other tab, but name 2 does not work - even when it is also name 2 on the other tabs. Here is what my VLOOKUP formula looks like: VLOOKUP($A$7:$A$33,'Event 1'!$A$17:$G$67,7,FALSE) And my IF statement is this: =IF(VLOOKUP($A$7:$A$33,'Event 1'!$A$17:$G$67,7,FALSE)0.01,VLOOKUP($A$7:$A$33,'E vent 1'!$A$17:$G$17,7,FALSE),0) The VLOOKUP works (with the constraints above) but if the value is False, it does not return 0, it retruns #VALUE! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
Try entering as an array formula which it is.
CTRL + SHIFT + ENTER Gord Dibben MS Excel MVP On Mon, 5 Nov 2007 11:37:03 -0800, a wrote: I have tried that and it still reads #VALUE! "John Bundy" wrote: Try the iserror function, if it returns an error insert "" else vlookup =IF(ISERROR(VLOOKUP($A$7:$A$33,'Event 1'!$A$17:$G$67,7,FALSE)),"",VLOOKUP($A$7:$A$33,'Ev ent 1'!$A$17:$G$67,7,FALSE)) -- -John Please rate when your question is answered to help us and others know what is helpful. "a" wrote: I am trying to build a worksheet with 50 tabs and a summary page. Each contain lists of data. I want the summary page to look up and match the data on each sheet using employee names. I have been using the VLOOKUP formula, but am having troubles: I have 20 or so rows of data on the summary page. However, not every name appears on every worksheet. So if the name is not there, it returns #VALUE!, which makes it so my summing formula does not work. So I have also been trying to use an IF formula. Additionally, I can get one first name to work so long as it is also the first name on the other tab, but name 2 does not work - even when it is also name 2 on the other tabs. Here is what my VLOOKUP formula looks like: VLOOKUP($A$7:$A$33,'Event 1'!$A$17:$G$67,7,FALSE) And my IF statement is this: =IF(VLOOKUP($A$7:$A$33,'Event 1'!$A$17:$G$67,7,FALSE)0.01,VLOOKUP($A$7:$A$33,'E vent 1'!$A$17:$G$17,7,FALSE),0) The VLOOKUP works (with the constraints above) but if the value is False, it does not return 0, it retruns #VALUE! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |