|
Using VLOOKUP to locate an answer in one of multiple columns
I am using the following formula to get an answer from a single column of a
spreadsheet (column 2) =VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALSE) Would you please tell me how I should modify the formula to look in more than one column for the appropriate answer? (i.e. look in columns #2, #9, and #22) If possible I would also like the formula to make the answer cell blank if it is unable to locate an appropriate answer. Thanks. Note: I also posted this question in the Applications Errors section by accident... sorry |
Using VLOOKUP to locate an answer in one of multiple columns
The question is how do you identify the 'appropriate answer' for the vlookup.
When you do a look up, you are looking up a value in an array, so if the value does not exist in the first column of the array, then column number is irrelvent. For example: =VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALSE) Say that B3 equals "PJ". If PJ does not appear in column R of your array R3:AP4180, then picking a new column won't yield any results. There is a way to change arrays if the formula evaluates as an error. =IF(ISERROR(VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALS E)),IF(ISERROR(VLOOKUP(B3,Master!$A$3:$Z$4180,2,FA LSE)),IF(ISERROR(VLOOKUP(B3,Master!$AA$3:$AQ$4180, 2,FALSE)))) All you are doing here is saying if VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALSE) evaluates as an error, use the same formula in a different array. If the second array is an error, try the third one, as so on. Give that a shot. I just did the formula on the fly, so there may be a missing "(" some where -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "ksean" wrote: I am using the following formula to get an answer from a single column of a spreadsheet (column 2) =VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALSE) Would you please tell me how I should modify the formula to look in more than one column for the appropriate answer? (i.e. look in columns #2, #9, and #22) If possible I would also like the formula to make the answer cell blank if it is unable to locate an appropriate answer. Thanks. Note: I also posted this question in the Applications Errors section by accident... sorry |
Using VLOOKUP to locate an answer in one of multiple columns
I must be missing something the formula I have ended up with is
=IF(ISERROR(VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALS E))),IF(ISERROR(VLOOKUP(B3,Master!$Y$3:$AQ$4180,2, FALSE))),IF(ISERROR(VLOOKUP(B3,Master!$AF$3:$AQ$41 80,2,FALSE))) but it still doesn't work. Do you have any ideas what I am missing? "PJFry" wrote: The question is how do you identify the 'appropriate answer' for the vlookup. When you do a look up, you are looking up a value in an array, so if the value does not exist in the first column of the array, then column number is irrelvent. For example: =VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALSE) Say that B3 equals "PJ". If PJ does not appear in column R of your array R3:AP4180, then picking a new column won't yield any results. There is a way to change arrays if the formula evaluates as an error. =IF(ISERROR(VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALS E)),IF(ISERROR(VLOOKUP(B3,Master!$A$3:$Z$4180,2,FA LSE)),IF(ISERROR(VLOOKUP(B3,Master!$AA$3:$AQ$4180, 2,FALSE)))) All you are doing here is saying if VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALSE) evaluates as an error, use the same formula in a different array. If the second array is an error, try the third one, as so on. Give that a shot. I just did the formula on the fly, so there may be a missing "(" some where -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "ksean" wrote: I am using the following formula to get an answer from a single column of a spreadsheet (column 2) =VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALSE) Would you please tell me how I should modify the formula to look in more than one column for the appropriate answer? (i.e. look in columns #2, #9, and #22) If possible I would also like the formula to make the answer cell blank if it is unable to locate an appropriate answer. Thanks. Note: I also posted this question in the Applications Errors section by accident... sorry |
Using VLOOKUP to locate an answer in one of multiple columns
Try this:
=IF(ISERROR(VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALS E)),IF(ISERROR(VLOOKUP(B4,Master!$Y$3:$AQ$4180,2,F ALSE)),IF(ISERROR(VLOOKUP(B5,Master!$AF$3:$AQ$4180 ,2,FALSE)),"Not Found",VLOOKUP(B5,Master!$AF$3:$AQ$4180,2,FALSE)), VLOOKUP(B4,Master!$Y$3:$AQ$4180,2,FALSE)),VLOOKUP( B3,Master!$R$3:$AP$4180,2,FALSE)) The problem was that there was no 'false' statements for the if(iserrors. Post back and let me know if that works. -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "ksean" wrote: I must be missing something the formula I have ended up with is =IF(ISERROR(VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALS E))),IF(ISERROR(VLOOKUP(B3,Master!$Y$3:$AQ$4180,2, FALSE))),IF(ISERROR(VLOOKUP(B3,Master!$AF$3:$AQ$41 80,2,FALSE))) but it still doesn't work. Do you have any ideas what I am missing? "PJFry" wrote: The question is how do you identify the 'appropriate answer' for the vlookup. When you do a look up, you are looking up a value in an array, so if the value does not exist in the first column of the array, then column number is irrelvent. For example: =VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALSE) Say that B3 equals "PJ". If PJ does not appear in column R of your array R3:AP4180, then picking a new column won't yield any results. There is a way to change arrays if the formula evaluates as an error. =IF(ISERROR(VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALS E)),IF(ISERROR(VLOOKUP(B3,Master!$A$3:$Z$4180,2,FA LSE)),IF(ISERROR(VLOOKUP(B3,Master!$AA$3:$AQ$4180, 2,FALSE)))) All you are doing here is saying if VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALSE) evaluates as an error, use the same formula in a different array. If the second array is an error, try the third one, as so on. Give that a shot. I just did the formula on the fly, so there may be a missing "(" some where -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "ksean" wrote: I am using the following formula to get an answer from a single column of a spreadsheet (column 2) =VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALSE) Would you please tell me how I should modify the formula to look in more than one column for the appropriate answer? (i.e. look in columns #2, #9, and #22) If possible I would also like the formula to make the answer cell blank if it is unable to locate an appropriate answer. Thanks. Note: I also posted this question in the Applications Errors section by accident... sorry |
Using VLOOKUP to locate an answer in one of multiple columns
Correction:
=IF(ISERROR(VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALS E)),IF(ISERROR(VLOOKUP(B3,Master!$Y$3:$AQ$4180,2,F ALSE)),IF(ISERROR(VLOOKUP(B3,Master!$AF$3:$AQ$4180 ,2,FALSE)),"Not Found",VLOOKUP(B3,Master!$AF$3:$AQ$4180,2,FALSE)), VLOOKUP(B3,Master!$Y$3:$AQ$4180,2,FALSE)),VLOOKUP( B3,Master!$R$3:$AP$4180,2,FALSE)) -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "ksean" wrote: I must be missing something the formula I have ended up with is =IF(ISERROR(VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALS E))),IF(ISERROR(VLOOKUP(B3,Master!$Y$3:$AQ$4180,2, FALSE))),IF(ISERROR(VLOOKUP(B3,Master!$AF$3:$AQ$41 80,2,FALSE))) but it still doesn't work. Do you have any ideas what I am missing? "PJFry" wrote: The question is how do you identify the 'appropriate answer' for the vlookup. When you do a look up, you are looking up a value in an array, so if the value does not exist in the first column of the array, then column number is irrelvent. For example: =VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALSE) Say that B3 equals "PJ". If PJ does not appear in column R of your array R3:AP4180, then picking a new column won't yield any results. There is a way to change arrays if the formula evaluates as an error. =IF(ISERROR(VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALS E)),IF(ISERROR(VLOOKUP(B3,Master!$A$3:$Z$4180,2,FA LSE)),IF(ISERROR(VLOOKUP(B3,Master!$AA$3:$AQ$4180, 2,FALSE)))) All you are doing here is saying if VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALSE) evaluates as an error, use the same formula in a different array. If the second array is an error, try the third one, as so on. Give that a shot. I just did the formula on the fly, so there may be a missing "(" some where -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "ksean" wrote: I am using the following formula to get an answer from a single column of a spreadsheet (column 2) =VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALSE) Would you please tell me how I should modify the formula to look in more than one column for the appropriate answer? (i.e. look in columns #2, #9, and #22) If possible I would also like the formula to make the answer cell blank if it is unable to locate an appropriate answer. Thanks. Note: I also posted this question in the Applications Errors section by accident... sorry |
Using VLOOKUP to locate an answer in one of multiple columns
Thank you for all of your hard work it is very much appreciated however I was
still not able to obtain the solution I was looking for and I suspect it was due to the poor quality of my explanation of my delema therefore I think that I need to re-explain my delemma. I have included a pair of sample tables (sheets) that are small examples of what I am trying to do. Using the data from the MASTER sheet answer the question marks on the ANSWER sheet, note that at any given time I may rearrange the ID Numbers in columns A, D & G of the MASTER sheet so the ANSWER sheet will have to automatically adjust to my revisions. MASTER sheet A B C D E F G H I ID No. Last Name First Name ID No. Last Name First Name ID No. Last Name First Name 1 Calvert Michael 2 Reimer Connor Keating Kyler Pell Derek Hardick Joshua 4 McKee Drew 10 Pell Trevor 14 Minchau Jennifer McKee Jimmy McPherson Jessica 11 Horner Cheyenne 9 Norman Trey 6 Quinlan Cody Dewilde Tylen 13 Olson Jessie Calef Duncan 7 Gray Braden 3 Orchard Aedan 16 Calef Brenden Hagstrom Graham Orchard Garrett 5 Calef Wesley Houle Erin Quaghebeur Justin Smith Alex 12 Keating Kassandra 8 Reid Andrew ANSWER sheet A B C Last Name First Name 1 ? ? 2 ? ? 3 ? ? 4 ? ? 5 ? ? 6 ? ? 7 ? ? 8 ? ? 9 ? ? 10 ? ? 11 ? ? 12 ? ? 13 ? ? 14 ? ? I hope that this helps to clarify my delemma and I appreciate any ideas you might have that will lead me to solving it. Thanks ksean "PJFry" wrote: Correction: =IF(ISERROR(VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALS E)),IF(ISERROR(VLOOKUP(B3,Master!$Y$3:$AQ$4180,2,F ALSE)),IF(ISERROR(VLOOKUP(B3,Master!$AF$3:$AQ$4180 ,2,FALSE)),"Not Found",VLOOKUP(B3,Master!$AF$3:$AQ$4180,2,FALSE)), VLOOKUP(B3,Master!$Y$3:$AQ$4180,2,FALSE)),VLOOKUP( B3,Master!$R$3:$AP$4180,2,FALSE)) -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "ksean" wrote: I must be missing something the formula I have ended up with is =IF(ISERROR(VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALS E))),IF(ISERROR(VLOOKUP(B3,Master!$Y$3:$AQ$4180,2, FALSE))),IF(ISERROR(VLOOKUP(B3,Master!$AF$3:$AQ$41 80,2,FALSE))) but it still doesn't work. Do you have any ideas what I am missing? "PJFry" wrote: The question is how do you identify the 'appropriate answer' for the vlookup. When you do a look up, you are looking up a value in an array, so if the value does not exist in the first column of the array, then column number is irrelvent. For example: =VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALSE) Say that B3 equals "PJ". If PJ does not appear in column R of your array R3:AP4180, then picking a new column won't yield any results. There is a way to change arrays if the formula evaluates as an error. =IF(ISERROR(VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALS E)),IF(ISERROR(VLOOKUP(B3,Master!$A$3:$Z$4180,2,FA LSE)),IF(ISERROR(VLOOKUP(B3,Master!$AA$3:$AQ$4180, 2,FALSE)))) All you are doing here is saying if VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALSE) evaluates as an error, use the same formula in a different array. If the second array is an error, try the third one, as so on. Give that a shot. I just did the formula on the fly, so there may be a missing "(" some where -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "ksean" wrote: I am using the following formula to get an answer from a single column of a spreadsheet (column 2) =VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALSE) Would you please tell me how I should modify the formula to look in more than one column for the appropriate answer? (i.e. look in columns #2, #9, and #22) If possible I would also like the formula to make the answer cell blank if it is unable to locate an appropriate answer. Thanks. Note: I also posted this question in the Applications Errors section by accident... sorry |
Using VLOOKUP to locate an answer in one of multiple columns
I tried your formula again with a fresher mind and it appears to work
"AWESOME!!!!" I just need to be more carefull in how I adapt it to each cell. If I have any further issues with it I will let you know but for now thank you very very much for your assistance Thanks "ksean" wrote: Thank you for all of your hard work it is very much appreciated however I was still not able to obtain the solution I was looking for and I suspect it was due to the poor quality of my explanation of my delema therefore I think that I need to re-explain my delemma. I have included a pair of sample tables (sheets) that are small examples of what I am trying to do. Using the data from the MASTER sheet answer the question marks on the ANSWER sheet, note that at any given time I may rearrange the ID Numbers in columns A, D & G of the MASTER sheet so the ANSWER sheet will have to automatically adjust to my revisions. MASTER sheet A B C D E F G H I ID No. Last Name First Name ID No. Last Name First Name ID No. Last Name First Name 1 Calvert Michael 2 Reimer Connor Keating Kyler Pell Derek Hardick Joshua 4 McKee Drew 10 Pell Trevor 14 Minchau Jennifer McKee Jimmy McPherson Jessica 11 Horner Cheyenne 9 Norman Trey 6 Quinlan Cody Dewilde Tylen 13 Olson Jessie Calef Duncan 7 Gray Braden 3 Orchard Aedan 16 Calef Brenden Hagstrom Graham Orchard Garrett 5 Calef Wesley Houle Erin Quaghebeur Justin Smith Alex 12 Keating Kassandra 8 Reid Andrew ANSWER sheet A B C Last Name First Name 1 ? ? 2 ? ? 3 ? ? 4 ? ? 5 ? ? 6 ? ? 7 ? ? 8 ? ? 9 ? ? 10 ? ? 11 ? ? 12 ? ? 13 ? ? 14 ? ? I hope that this helps to clarify my delemma and I appreciate any ideas you might have that will lead me to solving it. Thanks ksean "PJFry" wrote: Correction: =IF(ISERROR(VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALS E)),IF(ISERROR(VLOOKUP(B3,Master!$Y$3:$AQ$4180,2,F ALSE)),IF(ISERROR(VLOOKUP(B3,Master!$AF$3:$AQ$4180 ,2,FALSE)),"Not Found",VLOOKUP(B3,Master!$AF$3:$AQ$4180,2,FALSE)), VLOOKUP(B3,Master!$Y$3:$AQ$4180,2,FALSE)),VLOOKUP( B3,Master!$R$3:$AP$4180,2,FALSE)) -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "ksean" wrote: I must be missing something the formula I have ended up with is =IF(ISERROR(VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALS E))),IF(ISERROR(VLOOKUP(B3,Master!$Y$3:$AQ$4180,2, FALSE))),IF(ISERROR(VLOOKUP(B3,Master!$AF$3:$AQ$41 80,2,FALSE))) but it still doesn't work. Do you have any ideas what I am missing? "PJFry" wrote: The question is how do you identify the 'appropriate answer' for the vlookup. When you do a look up, you are looking up a value in an array, so if the value does not exist in the first column of the array, then column number is irrelvent. For example: =VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALSE) Say that B3 equals "PJ". If PJ does not appear in column R of your array R3:AP4180, then picking a new column won't yield any results. There is a way to change arrays if the formula evaluates as an error. =IF(ISERROR(VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALS E)),IF(ISERROR(VLOOKUP(B3,Master!$A$3:$Z$4180,2,FA LSE)),IF(ISERROR(VLOOKUP(B3,Master!$AA$3:$AQ$4180, 2,FALSE)))) All you are doing here is saying if VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALSE) evaluates as an error, use the same formula in a different array. If the second array is an error, try the third one, as so on. Give that a shot. I just did the formula on the fly, so there may be a missing "(" some where -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "ksean" wrote: I am using the following formula to get an answer from a single column of a spreadsheet (column 2) =VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALSE) Would you please tell me how I should modify the formula to look in more than one column for the appropriate answer? (i.e. look in columns #2, #9, and #22) If possible I would also like the formula to make the answer cell blank if it is unable to locate an appropriate answer. Thanks. Note: I also posted this question in the Applications Errors section by accident... sorry |
Using VLOOKUP to locate an answer in one of multiple columns
Very good!
-- Regards, PJ Please rate this post using the vote buttons if it was helpful. "ksean" wrote: I tried your formula again with a fresher mind and it appears to work "AWESOME!!!!" I just need to be more carefull in how I adapt it to each cell. If I have any further issues with it I will let you know but for now thank you very very much for your assistance Thanks "ksean" wrote: Thank you for all of your hard work it is very much appreciated however I was still not able to obtain the solution I was looking for and I suspect it was due to the poor quality of my explanation of my delema therefore I think that I need to re-explain my delemma. I have included a pair of sample tables (sheets) that are small examples of what I am trying to do. Using the data from the MASTER sheet answer the question marks on the ANSWER sheet, note that at any given time I may rearrange the ID Numbers in columns A, D & G of the MASTER sheet so the ANSWER sheet will have to automatically adjust to my revisions. MASTER sheet A B C D E F G H I ID No. Last Name First Name ID No. Last Name First Name ID No. Last Name First Name 1 Calvert Michael 2 Reimer Connor Keating Kyler Pell Derek Hardick Joshua 4 McKee Drew 10 Pell Trevor 14 Minchau Jennifer McKee Jimmy McPherson Jessica 11 Horner Cheyenne 9 Norman Trey 6 Quinlan Cody Dewilde Tylen 13 Olson Jessie Calef Duncan 7 Gray Braden 3 Orchard Aedan 16 Calef Brenden Hagstrom Graham Orchard Garrett 5 Calef Wesley Houle Erin Quaghebeur Justin Smith Alex 12 Keating Kassandra 8 Reid Andrew ANSWER sheet A B C Last Name First Name 1 ? ? 2 ? ? 3 ? ? 4 ? ? 5 ? ? 6 ? ? 7 ? ? 8 ? ? 9 ? ? 10 ? ? 11 ? ? 12 ? ? 13 ? ? 14 ? ? I hope that this helps to clarify my delemma and I appreciate any ideas you might have that will lead me to solving it. Thanks ksean "PJFry" wrote: Correction: =IF(ISERROR(VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALS E)),IF(ISERROR(VLOOKUP(B3,Master!$Y$3:$AQ$4180,2,F ALSE)),IF(ISERROR(VLOOKUP(B3,Master!$AF$3:$AQ$4180 ,2,FALSE)),"Not Found",VLOOKUP(B3,Master!$AF$3:$AQ$4180,2,FALSE)), VLOOKUP(B3,Master!$Y$3:$AQ$4180,2,FALSE)),VLOOKUP( B3,Master!$R$3:$AP$4180,2,FALSE)) -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "ksean" wrote: I must be missing something the formula I have ended up with is =IF(ISERROR(VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALS E))),IF(ISERROR(VLOOKUP(B3,Master!$Y$3:$AQ$4180,2, FALSE))),IF(ISERROR(VLOOKUP(B3,Master!$AF$3:$AQ$41 80,2,FALSE))) but it still doesn't work. Do you have any ideas what I am missing? "PJFry" wrote: The question is how do you identify the 'appropriate answer' for the vlookup. When you do a look up, you are looking up a value in an array, so if the value does not exist in the first column of the array, then column number is irrelvent. For example: =VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALSE) Say that B3 equals "PJ". If PJ does not appear in column R of your array R3:AP4180, then picking a new column won't yield any results. There is a way to change arrays if the formula evaluates as an error. =IF(ISERROR(VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALS E)),IF(ISERROR(VLOOKUP(B3,Master!$A$3:$Z$4180,2,FA LSE)),IF(ISERROR(VLOOKUP(B3,Master!$AA$3:$AQ$4180, 2,FALSE)))) All you are doing here is saying if VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALSE) evaluates as an error, use the same formula in a different array. If the second array is an error, try the third one, as so on. Give that a shot. I just did the formula on the fly, so there may be a missing "(" some where -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "ksean" wrote: I am using the following formula to get an answer from a single column of a spreadsheet (column 2) =VLOOKUP(B3,Master!$R$3:$AP$4180,2,FALSE) Would you please tell me how I should modify the formula to look in more than one column for the appropriate answer? (i.e. look in columns #2, #9, and #22) If possible I would also like the formula to make the answer cell blank if it is unable to locate an appropriate answer. Thanks. Note: I also posted this question in the Applications Errors section by accident... sorry |
| All times are GMT +1. The time now is 09:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com