| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP
Hi is there a way to add multiple VLOOKUP to one cell like below.
=VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE) and add this to the cell as well VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE) Many thanks Mark |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP
you can concatenate ---
=VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE) & VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE) "terilad" wrote in message ... Hi is there a way to add multiple VLOOKUP to one cell like below. =VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE) and add this to the cell as well VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE) Many thanks Mark |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP
Thanks for your response, it has to only return one entry, so it has to
lookup all cells but only return what entry is in cell, it has to be or not all of data. =VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE) or VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE) But this does not work. Any other ideas. Thanks Mark "Patrick Molloy" wrote: you can concatenate --- =VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE) & VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE) "terilad" wrote in message ... Hi is there a way to add multiple VLOOKUP to one cell like below. =VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE) and add this to the cell as well VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE) Many thanks Mark |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP
Untested - please double check parentheses:
=IF(ISNA(VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE)),VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE), VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE))) If the D8 lookup fails, use the E10 lookup If both fail you will get #N/A Avoid this with =IF(ISNA(VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE)), IF(ISNA(VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE),"Neither" ,VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE))), VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE))) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "terilad" wrote in message ... Hi is there a way to add multiple VLOOKUP to one cell like below. =VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE) and add this to the cell as well VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE) Many thanks Mark |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP
Can you have a look at the following code, I am trying to enter but getting
an error with formula =IF(ISNA(VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE)),VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE)),VLOOKUP('Kelso Resources'!D14,C25:G25,3,false)),VLOOKUP('Kelso Resources'!D18,C25:G25,4,false)),VLOOKUP('Kelso Resources'!D24,C25:G25,3.false)),VLOOKUP('Kelso Resources'!D28,C25:G25,2,false)),VLOOKUP('Kelso Resources'!E30,C25:G25,5,false)),VLOOKUP('Kelso Resources'!D34,C25:G25,4,false)),VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE) Many thanks Mark "Bernard Liengme" wrote: Untested - please double check parentheses: =IF(ISNA(VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE)),VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE), VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE))) If the D8 lookup fails, use the E10 lookup If both fail you will get #N/A Avoid this with =IF(ISNA(VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE)), IF(ISNA(VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE),"Neither" ,VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE))), VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE))) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "terilad" wrote in message ... Hi is there a way to add multiple VLOOKUP to one cell like below. =VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE) and add this to the cell as well VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE) Many thanks Mark |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP
What I am trying to do is I have a shift resource of 17 weeks Monday -
Sunday, and a review to quick glance and see what people are working in that week, so when I input a person into a cell for 15:00 - 23:00 on a Monday it will report 15:00 - 23:00 in the cell under Monday, this has to be variable as other cells to lookup have other shift times, but I need the correct shift time to report in the quick glance review sheet as this is needed to total the number of shifts each person works in a week. Can you assist me with some help? Regards Mark "Patrick Molloy" wrote: good grief! you've gone from looking up two columns to multiple columns it seems! nested IFs then become totally unwieldy. Write a UDF instead generally a VLOOKUP returns an error if the item being looked for doesn't exist so "most" sheet formula are written as Bernard showed NOT =VLOOKUP(what, where, columnA, false) but to check for the error , in your case, lookup something else = IF ( ISERROR (vlookup1) , vlookup2, vlookup1) and one more error check = IF ( ISERROR (vlookup1) , IF (iserror (vlookup2) , "NEITHER", vlookup2) , vlookup1) (Bernard's formula below) what exactly are you trying to achieve? "terilad" wrote in message ... Can you have a look at the following code, I am trying to enter but getting an error with formula =IF(ISNA(VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE)),VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE)),VLOOKUP('Kelso Resources'!D14,C25:G25,3,false)),VLOOKUP('Kelso Resources'!D18,C25:G25,4,false)),VLOOKUP('Kelso Resources'!D24,C25:G25,3.false)),VLOOKUP('Kelso Resources'!D28,C25:G25,2,false)),VLOOKUP('Kelso Resources'!E30,C25:G25,5,false)),VLOOKUP('Kelso Resources'!D34,C25:G25,4,false)),VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE) Many thanks Mark "Bernard Liengme" wrote: Untested - please double check parentheses: =IF(ISNA(VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE)),VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE), VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE))) If the D8 lookup fails, use the E10 lookup If both fail you will get #N/A Avoid this with =IF(ISNA(VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE)), IF(ISNA(VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE),"Neither" ,VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE))), VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE))) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "terilad" wrote in message ... Hi is there a way to add multiple VLOOKUP to one cell like below. =VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE) and add this to the cell as well VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE) Many thanks Mark |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP
sorry, thats still not clear. remember that we cannot see what you're
looking at so my interp is a table where the first column is the shift, then the 2nd - 80th represent each day the first row is headers the 2nd & following rows start with the shift, then under each day, the person doing the shift you need a summary I have dropped a demo file he http://cid-b8e56c9a5f311cb7.skydrive...fts/shifts.xls is this anything like what you need? "terilad" wrote in message ... What I am trying to do is I have a shift resource of 17 weeks Monday - Sunday, and a review to quick glance and see what people are working in that week, so when I input a person into a cell for 15:00 - 23:00 on a Monday it will report 15:00 - 23:00 in the cell under Monday, this has to be variable as other cells to lookup have other shift times, but I need the correct shift time to report in the quick glance review sheet as this is needed to total the number of shifts each person works in a week. Can you assist me with some help? Regards Mark "Patrick Molloy" wrote: good grief! you've gone from looking up two columns to multiple columns it seems! nested IFs then become totally unwieldy. Write a UDF instead generally a VLOOKUP returns an error if the item being looked for doesn't exist so "most" sheet formula are written as Bernard showed NOT =VLOOKUP(what, where, columnA, false) but to check for the error , in your case, lookup something else = IF ( ISERROR (vlookup1) , vlookup2, vlookup1) and one more error check = IF ( ISERROR (vlookup1) , IF (iserror (vlookup2) , "NEITHER", vlookup2) , vlookup1) (Bernard's formula below) what exactly are you trying to achieve? "terilad" wrote in message ... Can you have a look at the following code, I am trying to enter but getting an error with formula =IF(ISNA(VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE)),VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE)),VLOOKUP('Kelso Resources'!D14,C25:G25,3,false)),VLOOKUP('Kelso Resources'!D18,C25:G25,4,false)),VLOOKUP('Kelso Resources'!D24,C25:G25,3.false)),VLOOKUP('Kelso Resources'!D28,C25:G25,2,false)),VLOOKUP('Kelso Resources'!E30,C25:G25,5,false)),VLOOKUP('Kelso Resources'!D34,C25:G25,4,false)),VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE) Many thanks Mark "Bernard Liengme" wrote: Untested - please double check parentheses: =IF(ISNA(VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE)),VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE), VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE))) If the D8 lookup fails, use the E10 lookup If both fail you will get #N/A Avoid this with =IF(ISNA(VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE)), IF(ISNA(VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE),"Neither" ,VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE))), VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE))) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "terilad" wrote in message ... Hi is there a way to add multiple VLOOKUP to one cell like below. =VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE) and add this to the cell as well VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE) Many thanks Mark |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP
Do you have an email address and I can send you the file with an explanation
of what I looking to do? Many thanks Mark "Patrick Molloy" wrote: sorry, thats still not clear. remember that we cannot see what you're looking at so my interp is a table where the first column is the shift, then the 2nd - 80th represent each day the first row is headers the 2nd & following rows start with the shift, then under each day, the person doing the shift you need a summary I have dropped a demo file he http://cid-b8e56c9a5f311cb7.skydrive...fts/shifts.xls is this anything like what you need? "terilad" wrote in message ... What I am trying to do is I have a shift resource of 17 weeks Monday - Sunday, and a review to quick glance and see what people are working in that week, so when I input a person into a cell for 15:00 - 23:00 on a Monday it will report 15:00 - 23:00 in the cell under Monday, this has to be variable as other cells to lookup have other shift times, but I need the correct shift time to report in the quick glance review sheet as this is needed to total the number of shifts each person works in a week. Can you assist me with some help? Regards Mark "Patrick Molloy" wrote: good grief! you've gone from looking up two columns to multiple columns it seems! nested IFs then become totally unwieldy. Write a UDF instead generally a VLOOKUP returns an error if the item being looked for doesn't exist so "most" sheet formula are written as Bernard showed NOT =VLOOKUP(what, where, columnA, false) but to check for the error , in your case, lookup something else = IF ( ISERROR (vlookup1) , vlookup2, vlookup1) and one more error check = IF ( ISERROR (vlookup1) , IF (iserror (vlookup2) , "NEITHER", vlookup2) , vlookup1) (Bernard's formula below) what exactly are you trying to achieve? "terilad" wrote in message ... Can you have a look at the following code, I am trying to enter but getting an error with formula =IF(ISNA(VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE)),VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE)),VLOOKUP('Kelso Resources'!D14,C25:G25,3,false)),VLOOKUP('Kelso Resources'!D18,C25:G25,4,false)),VLOOKUP('Kelso Resources'!D24,C25:G25,3.false)),VLOOKUP('Kelso Resources'!D28,C25:G25,2,false)),VLOOKUP('Kelso Resources'!E30,C25:G25,5,false)),VLOOKUP('Kelso Resources'!D34,C25:G25,4,false)),VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE) Many thanks Mark "Bernard Liengme" wrote: Untested - please double check parentheses: =IF(ISNA(VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE)),VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE), VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE))) If the D8 lookup fails, use the E10 lookup If both fail you will get #N/A Avoid this with =IF(ISNA(VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE)), IF(ISNA(VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE),"Neither" ,VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE))), VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE))) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "terilad" wrote in message ... Hi is there a way to add multiple VLOOKUP to one cell like below. =VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE) and add this to the cell as well VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE) Many thanks Mark |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP
patrick_molloyATNOSPAMhotmail.com
"terilad" wrote in message ... Do you have an email address and I can send you the file with an explanation of what I looking to do? Many thanks Mark "Patrick Molloy" wrote: sorry, thats still not clear. remember that we cannot see what you're looking at so my interp is a table where the first column is the shift, then the 2nd - 80th represent each day the first row is headers the 2nd & following rows start with the shift, then under each day, the person doing the shift you need a summary I have dropped a demo file he http://cid-b8e56c9a5f311cb7.skydrive...fts/shifts.xls is this anything like what you need? "terilad" wrote in message ... What I am trying to do is I have a shift resource of 17 weeks Monday - Sunday, and a review to quick glance and see what people are working in that week, so when I input a person into a cell for 15:00 - 23:00 on a Monday it will report 15:00 - 23:00 in the cell under Monday, this has to be variable as other cells to lookup have other shift times, but I need the correct shift time to report in the quick glance review sheet as this is needed to total the number of shifts each person works in a week. Can you assist me with some help? Regards Mark "Patrick Molloy" wrote: good grief! you've gone from looking up two columns to multiple columns it seems! nested IFs then become totally unwieldy. Write a UDF instead generally a VLOOKUP returns an error if the item being looked for doesn't exist so "most" sheet formula are written as Bernard showed NOT =VLOOKUP(what, where, columnA, false) but to check for the error , in your case, lookup something else = IF ( ISERROR (vlookup1) , vlookup2, vlookup1) and one more error check = IF ( ISERROR (vlookup1) , IF (iserror (vlookup2) , "NEITHER", vlookup2) , vlookup1) (Bernard's formula below) what exactly are you trying to achieve? "terilad" wrote in message ... Can you have a look at the following code, I am trying to enter but getting an error with formula =IF(ISNA(VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE)),VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE)),VLOOKUP('Kelso Resources'!D14,C25:G25,3,false)),VLOOKUP('Kelso Resources'!D18,C25:G25,4,false)),VLOOKUP('Kelso Resources'!D24,C25:G25,3.false)),VLOOKUP('Kelso Resources'!D28,C25:G25,2,false)),VLOOKUP('Kelso Resources'!E30,C25:G25,5,false)),VLOOKUP('Kelso Resources'!D34,C25:G25,4,false)),VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE) Many thanks Mark "Bernard Liengme" wrote: Untested - please double check parentheses: =IF(ISNA(VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE)),VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE), VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE))) If the D8 lookup fails, use the E10 lookup If both fail you will get #N/A Avoid this with =IF(ISNA(VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE)), IF(ISNA(VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE),"Neither" ,VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE))), VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE))) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "terilad" wrote in message ... Hi is there a way to add multiple VLOOKUP to one cell like below. =VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE) and add this to the cell as well VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE) Many thanks Mark |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP
I have sent you the file.
Many thanks Mark "Patrick Molloy" wrote: patrick_molloyATNOSPAMhotmail.com "terilad" wrote in message ... Do you have an email address and I can send you the file with an explanation of what I looking to do? Many thanks Mark "Patrick Molloy" wrote: sorry, thats still not clear. remember that we cannot see what you're looking at so my interp is a table where the first column is the shift, then the 2nd - 80th represent each day the first row is headers the 2nd & following rows start with the shift, then under each day, the person doing the shift you need a summary I have dropped a demo file he http://cid-b8e56c9a5f311cb7.skydrive...fts/shifts.xls is this anything like what you need? "terilad" wrote in message ... What I am trying to do is I have a shift resource of 17 weeks Monday - Sunday, and a review to quick glance and see what people are working in that week, so when I input a person into a cell for 15:00 - 23:00 on a Monday it will report 15:00 - 23:00 in the cell under Monday, this has to be variable as other cells to lookup have other shift times, but I need the correct shift time to report in the quick glance review sheet as this is needed to total the number of shifts each person works in a week. Can you assist me with some help? Regards Mark "Patrick Molloy" wrote: good grief! you've gone from looking up two columns to multiple columns it seems! nested IFs then become totally unwieldy. Write a UDF instead generally a VLOOKUP returns an error if the item being looked for doesn't exist so "most" sheet formula are written as Bernard showed NOT =VLOOKUP(what, where, columnA, false) but to check for the error , in your case, lookup something else = IF ( ISERROR (vlookup1) , vlookup2, vlookup1) and one more error check = IF ( ISERROR (vlookup1) , IF (iserror (vlookup2) , "NEITHER", vlookup2) , vlookup1) (Bernard's formula below) what exactly are you trying to achieve? "terilad" wrote in message ... Can you have a look at the following code, I am trying to enter but getting an error with formula =IF(ISNA(VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE)),VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE)),VLOOKUP('Kelso Resources'!D14,C25:G25,3,false)),VLOOKUP('Kelso Resources'!D18,C25:G25,4,false)),VLOOKUP('Kelso Resources'!D24,C25:G25,3.false)),VLOOKUP('Kelso Resources'!D28,C25:G25,2,false)),VLOOKUP('Kelso Resources'!E30,C25:G25,5,false)),VLOOKUP('Kelso Resources'!D34,C25:G25,4,false)),VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE) Many thanks Mark "Bernard Liengme" wrote: Untested - please double check parentheses: =IF(ISNA(VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE)),VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE), VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE))) If the D8 lookup fails, use the E10 lookup If both fail you will get #N/A Avoid this with =IF(ISNA(VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE)), IF(ISNA(VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE),"Neither" ,VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE))), VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE))) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "terilad" wrote in message ... Hi is there a way to add multiple VLOOKUP to one cell like below. =VLOOKUP('Kelso Resources'!D8,C25:G25,2,FALSE) and add this to the cell as well VLOOKUP('Kelso Resources'!E10,C25:G25,5,FALSE) Many thanks Mark |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
| using a vlookup to enter text into rows beneath the vlookup cell | Excel Programming | |||
| Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
| Which is faster: VLOOKUP-worksheet or VB-array VLOOKUP? | Excel Programming | |||
| Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
Linear Mode
