A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Using VLOOKUP to locate an answer in one of multiple columns



 
 
Thread Tools Display Modes
  #1  
Old April 20th, 2009, 08:18 PM posted to microsoft.public.excel.worksheet.functions
ksean
external usenet poster
 
Posts: 62
Default 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
  #2  
Old April 21st, 2009, 12:48 AM posted to microsoft.public.excel.worksheet.functions
PJFry
external usenet poster
 
Posts: 148
Default 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

  #3  
Old April 21st, 2009, 05:59 AM posted to microsoft.public.excel.worksheet.functions
ksean
external usenet poster
 
Posts: 62
Default 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

  #4  
Old April 21st, 2009, 05:02 PM posted to microsoft.public.excel.worksheet.functions
PJFry
external usenet poster
 
Posts: 148
Default 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

  #5  
Old April 21st, 2009, 05:03 PM posted to microsoft.public.excel.worksheet.functions
PJFry
external usenet poster
 
Posts: 148
Default 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

  #6  
Old April 21st, 2009, 06:08 PM posted to microsoft.public.excel.worksheet.functions
ksean
external usenet poster
 
Posts: 62
Default 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

  #7  
Old April 21st, 2009, 07:06 PM posted to microsoft.public.excel.worksheet.functions
ksean
external usenet poster
 
Posts: 62
Default 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

  #8  
Old April 22nd, 2009, 09:08 PM posted to microsoft.public.excel.worksheet.functions
PJFry
external usenet poster
 
Posts: 148
Default 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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:31 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.