Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Function returning multiple values in a separate table
Hi everyone, hope someone out there can help me with this one, a bit long!
I'm a fairly new user and using Excell 2007. Outside the main body of my worksheet I have set a LookUp table, with 2 columns starting with cell AM1 and finishing with cell AN29, with precise values that will be needed in the spreadsheet: Col AM Col An 20 10 18 9 16 8.50 14 7.50 12 6.50 11 6.00 10 5.50 ..... .... etc. etc., to 2.00 1.25 1.88 1.20 1.75 1.00 1.63 0.91 1.50 0,83 in cell An29, this being the last value of the series. (A total of 29 values that are not all listed here). In my worksheet, the two columns involved a Col G - Manual data entry Entering randomly one value per cell out of the list in col AM of the LookUp table (or none) Col R - Calculated data Returning the equivalent value in col AN of the LookUp table (or none) *What I do - Row 7 : First entry In col R, cell R7, I enter the following function: =VLOOKUP(8,$AM$1:$AN$29,2,FALSE) (the figure "8" being temporary) and copy it right down the column (up to 400 entries) Then, for each new entry that I enter (manually) in each cell of col G, I modify the formula, in the same row in col R, to match my entry, in other words I delete the "8" and replace it with the figure I have just typed in: Example: Cell G7 : type in 16, Cell R7 : delete the 8 and replace by 16 to read: =VLOOKUP(16,$AM$1:$AN$29,2,FALSE) (which will give me the value 8.50 - col N of LookUp table) and so on (for up to 400 cells) until I have completed the entries; in the case of any blank cell in col G, I delete the formula in col R. The whole process is really time consuming and I make quite a few errors. *What I would like to achieve: Have a function (in column R?) that would automatically return, in the equivalent cell in col R, the relevant value that is listed in the column AN of the LookUp table, everytime a new entry is inserted in any of the cells of column G, but giving a blank when there are no equivalents. Sorry this is so longwinded, but I wanted to present this as clearly as possible so as not to confuse the issue in order to get an answer! Thank you for any help, as it will be much appreciated |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Function returning multiple values in a separate table
Hi,
I think you want to change your VLOOKUP to include a reference to column G Type below in R7: =VLOOKUP(G7,$AM$1:$AN$29,2,FALSE) You can then copy that down column R and the G7 will change automatically to G8,G9,G10.... Let me know if that's what you're looking for. "CAT" wrote: Hi everyone, hope someone out there can help me with this one, a bit long! I'm a fairly new user and using Excell 2007. Outside the main body of my worksheet I have set a LookUp table, with 2 columns starting with cell AM1 and finishing with cell AN29, with precise values that will be needed in the spreadsheet: Col AM Col An 20 10 18 9 16 8.50 14 7.50 12 6.50 11 6.00 10 5.50 .... .... etc. etc., to 2.00 1.25 1.88 1.20 1.75 1.00 1.63 0.91 1.50 0,83 in cell An29, this being the last value of the series. (A total of 29 values that are not all listed here). In my worksheet, the two columns involved a Col G - Manual data entry Entering randomly one value per cell out of the list in col AM of the LookUp table (or none) Col R - Calculated data Returning the equivalent value in col AN of the LookUp table (or none) *What I do - Row 7 : First entry In col R, cell R7, I enter the following function: =VLOOKUP(8,$AM$1:$AN$29,2,FALSE) (the figure "8" being temporary) and copy it right down the column (up to 400 entries) Then, for each new entry that I enter (manually) in each cell of col G, I modify the formula, in the same row in col R, to match my entry, in other words I delete the "8" and replace it with the figure I have just typed in: Example: Cell G7 : type in 16, Cell R7 : delete the 8 and replace by 16 to read: =VLOOKUP(16,$AM$1:$AN$29,2,FALSE) (which will give me the value 8.50 - col N of LookUp table) and so on (for up to 400 cells) until I have completed the entries; in the case of any blank cell in col G, I delete the formula in col R. The whole process is really time consuming and I make quite a few errors. *What I would like to achieve: Have a function (in column R?) that would automatically return, in the equivalent cell in col R, the relevant value that is listed in the column AN of the LookUp table, everytime a new entry is inserted in any of the cells of column G, but giving a blank when there are no equivalents. Sorry this is so longwinded, but I wanted to present this as clearly as possible so as not to confuse the issue in order to get an answer! Thank you for any help, as it will be much appreciated |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Function returning multiple values in a separate table
Hi H2fcell,
Thanks a lot; that works great, I am getting exactly what I was looking for and it's so simple to enter. Just one thing: If I leave any cell in the G column blank, I am getting a //N/A in column R and I wanted it blank, I suppose I could just erase the formula, since it does not happen that often and it does not seem to interfere with the working of it in the following cells. Comparing to what I had to do before,it's all so simple; thanks again for your help, you've saved me a lot of time. "h2fcell" wrote: Hi, I think you want to change your VLOOKUP to include a reference to column G Type below in R7: =VLOOKUP(G7,$AM$1:$AN$29,2,FALSE) You can then copy that down column R and the G7 will change automatically to G8,G9,G10.... Let me know if that's what you're looking for. "CAT" wrote: Hi everyone, hope someone out there can help me with this one, a bit long! I'm a fairly new user and using Excell 2007. Outside the main body of my worksheet I have set a LookUp table, with 2 columns starting with cell AM1 and finishing with cell AN29, with precise values that will be needed in the spreadsheet: Col AM Col An 20 10 18 9 16 8.50 14 7.50 12 6.50 11 6.00 10 5.50 .... .... etc. etc., to 2.00 1.25 1.88 1.20 1.75 1.00 1.63 0.91 1.50 0,83 in cell An29, this being the last value of the series. (A total of 29 values that are not all listed here). In my worksheet, the two columns involved a Col G - Manual data entry Entering randomly one value per cell out of the list in col AM of the LookUp table (or none) Col R - Calculated data Returning the equivalent value in col AN of the LookUp table (or none) *What I do - Row 7 : First entry In col R, cell R7, I enter the following function: =VLOOKUP(8,$AM$1:$AN$29,2,FALSE) (the figure "8" being temporary) and copy it right down the column (up to 400 entries) Then, for each new entry that I enter (manually) in each cell of col G, I modify the formula, in the same row in col R, to match my entry, in other words I delete the "8" and replace it with the figure I have just typed in: Example: Cell G7 : type in 16, Cell R7 : delete the 8 and replace by 16 to read: =VLOOKUP(16,$AM$1:$AN$29,2,FALSE) (which will give me the value 8.50 - col N of LookUp table) and so on (for up to 400 cells) until I have completed the entries; in the case of any blank cell in col G, I delete the formula in col R. The whole process is really time consuming and I make quite a few errors. *What I would like to achieve: Have a function (in column R?) that would automatically return, in the equivalent cell in col R, the relevant value that is listed in the column AN of the LookUp table, everytime a new entry is inserted in any of the cells of column G, but giving a blank when there are no equivalents. Sorry this is so longwinded, but I wanted to present this as clearly as possible so as not to confuse the issue in order to get an answer! Thank you for any help, as it will be much appreciated |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Function returning multiple values in a separate table
use
=if(ISNA(VLOOKUP(G7,$AM$1:$AN$29,2,FALSE))) "CAT" wrote: Hi H2fcell, Thanks a lot; that works great, I am getting exactly what I was looking for and it's so simple to enter. Just one thing: If I leave any cell in the G column blank, I am getting a //N/A in column R and I wanted it blank, I suppose I could just erase the formula, since it does not happen that often and it does not seem to interfere with the working of it in the following cells. Comparing to what I had to do before,it's all so simple; thanks again for your help, you've saved me a lot of time. "h2fcell" wrote: Hi, I think you want to change your VLOOKUP to include a reference to column G Type below in R7: =VLOOKUP(G7,$AM$1:$AN$29,2,FALSE) You can then copy that down column R and the G7 will change automatically to G8,G9,G10.... Let me know if that's what you're looking for. "CAT" wrote: Hi everyone, hope someone out there can help me with this one, a bit long! I'm a fairly new user and using Excell 2007. Outside the main body of my worksheet I have set a LookUp table, with 2 columns starting with cell AM1 and finishing with cell AN29, with precise values that will be needed in the spreadsheet: Col AM Col An 20 10 18 9 16 8.50 14 7.50 12 6.50 11 6.00 10 5.50 .... .... etc. etc., to 2.00 1.25 1.88 1.20 1.75 1.00 1.63 0.91 1.50 0,83 in cell An29, this being the last value of the series. (A total of 29 values that are not all listed here). In my worksheet, the two columns involved a Col G - Manual data entry Entering randomly one value per cell out of the list in col AM of the LookUp table (or none) Col R - Calculated data Returning the equivalent value in col AN of the LookUp table (or none) *What I do - Row 7 : First entry In col R, cell R7, I enter the following function: =VLOOKUP(8,$AM$1:$AN$29,2,FALSE) (the figure "8" being temporary) and copy it right down the column (up to 400 entries) Then, for each new entry that I enter (manually) in each cell of col G, I modify the formula, in the same row in col R, to match my entry, in other words I delete the "8" and replace it with the figure I have just typed in: Example: Cell G7 : type in 16, Cell R7 : delete the 8 and replace by 16 to read: =VLOOKUP(16,$AM$1:$AN$29,2,FALSE) (which will give me the value 8.50 - col N of LookUp table) and so on (for up to 400 cells) until I have completed the entries; in the case of any blank cell in col G, I delete the formula in col R. The whole process is really time consuming and I make quite a few errors. *What I would like to achieve: Have a function (in column R?) that would automatically return, in the equivalent cell in col R, the relevant value that is listed in the column AN of the LookUp table, everytime a new entry is inserted in any of the cells of column G, but giving a blank when there are no equivalents. Sorry this is so longwinded, but I wanted to present this as clearly as possible so as not to confuse the issue in order to get an answer! Thank you for any help, as it will be much appreciated |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Function returning multiple values in a separate table
Hi VMohan,
Thank you very much for taking the time. I have typed your formula in (several times, and carefully) but keep getting "error in formula" by Excell. Unfortunately, I wouldn't know where the error is as I am very much a newbie! For the record, what I am exactly getting in col R (with no data in col G) is as follows: (sign for number (don't have it on the keyboard), then attached to it: N/A. Does that make any sense? "vmohan1978" wrote: use =if(ISNA(VLOOKUP(G7,$AM$1:$AN$29,2,FALSE))) "CAT" wrote: Hi H2fcell, Thanks a lot; that works great, I am getting exactly what I was looking for and it's so simple to enter. Just one thing: If I leave any cell in the G column blank, I am getting a //N/A in column R and I wanted it blank, I suppose I could just erase the formula, since it does not happen that often and it does not seem to interfere with the working of it in the following cells. Comparing to what I had to do before,it's all so simple; thanks again for your help, you've saved me a lot of time. "h2fcell" wrote: Hi, I think you want to change your VLOOKUP to include a reference to column G Type below in R7: =VLOOKUP(G7,$AM$1:$AN$29,2,FALSE) You can then copy that down column R and the G7 will change automatically to G8,G9,G10.... Let me know if that's what you're looking for. "CAT" wrote: Hi everyone, hope someone out there can help me with this one, a bit long! I'm a fairly new user and using Excell 2007. Outside the main body of my worksheet I have set a LookUp table, with 2 columns starting with cell AM1 and finishing with cell AN29, with precise values that will be needed in the spreadsheet: Col AM Col An 20 10 18 9 16 8.50 14 7.50 12 6.50 11 6.00 10 5.50 .... .... etc. etc., to 2.00 1.25 1.88 1.20 1.75 1.00 1.63 0.91 1.50 0,83 in cell An29, this being the last value of the series. (A total of 29 values that are not all listed here). In my worksheet, the two columns involved a Col G - Manual data entry Entering randomly one value per cell out of the list in col AM of the LookUp table (or none) Col R - Calculated data Returning the equivalent value in col AN of the LookUp table (or none) *What I do - Row 7 : First entry In col R, cell R7, I enter the following function: =VLOOKUP(8,$AM$1:$AN$29,2,FALSE) (the figure "8" being temporary) and copy it right down the column (up to 400 entries) Then, for each new entry that I enter (manually) in each cell of col G, I modify the formula, in the same row in col R, to match my entry, in other words I delete the "8" and replace it with the figure I have just typed in: Example: Cell G7 : type in 16, Cell R7 : delete the 8 and replace by 16 to read: =VLOOKUP(16,$AM$1:$AN$29,2,FALSE) (which will give me the value 8.50 - col N of LookUp table) and so on (for up to 400 cells) until I have completed the entries; in the case of any blank cell in col G, I delete the formula in col R. The whole process is really time consuming and I make quite a few errors. *What I would like to achieve: Have a function (in column R?) that would automatically return, in the equivalent cell in col R, the relevant value that is listed in the column AN of the LookUp table, everytime a new entry is inserted in any of the cells of column G, but giving a blank when there are no equivalents. Sorry this is so longwinded, but I wanted to present this as clearly as possible so as not to confuse the issue in order to get an answer! Thank you for any help, as it will be much appreciated |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Function returning multiple values in a separate table
Well, you shouldn't be trying (however carefully) to type in a formula which
is given here. There is always a likelihood of typing errors, so instead of reading and typing, just copy the formula from here and paste it into the formula bar for the relevant cell on your worksheet. Having said that, the problem in this case wasn't yours, as the formula given by the previous poster had invalid syntax. I wonder whether he had intended not to say =if(ISNA(VLOOKUP(G7,$AM$1:$AN$29,2,FALSE))) but =IF(ISNA(VLOOKUP(G7,$AM$1:$AN$29,2,FALSE)),"",VLOO KUP(G7,$AM$1:$AN$29,2,FALSE))When Excel tell you that the formula is invalid, it tries to help by puttingthe cursor where the error is, and Excel help for the function in questionwill tell you the required syntax (and usually give you examples and in a"Show Also" link will often show related functions).In this case your IF function has a condition [the first parameter of theIF], but it doesn't have the consequence if true [2nd parameter of the IF],nor does it have the consequence if false [which is the 3rd parameter of theIF, which is optional in the syntax, but probably a significant parameter inthis case as the 2nd parameter is presumably handling the error case and youneed to handle the non-error case too].David Biddulph"CAT" wrote in ... Hi VMohan, Thank you very much for taking the time. I have typed your formula in (several times, and carefully) but keepgetting "error in formula" by Excell. Unfortunately, I wouldn't know where theerror is as I am very much a newbie! For the record, what I am exactly getting in col R (with no data in col G) is as follows: (sign for number (don't have it on the keyboard), then attached to it:N/A. Does that make any sense? "vmohan1978" wrote: use =if(ISNA(VLOOKUP(G7,$AM$1:$AN$29,2,FALSE))) "CAT" wrote: Hi H2fcell, Thanks a lot; that works great, I am getting exactly what I was lookingfor and it's so simple to enter. Just one thing: If I leave any cell in the G column blank, I am gettinga //N/A in column R and I wanted it blank, I suppose I could just erasethe formula, since it does not happen that often and it does not seem to interfere with the working of it in the following cells. Comparing to what I had to do before,it's all so simple; thanks againfor your help, you've saved me a lot of time. "h2fcell" wrote: Hi, I think you want to change your VLOOKUP to include a reference tocolumn G Type below in R7: =VLOOKUP(G7,$AM$1:$AN$29,2,FALSE) You can then copy that down column R and the G7 will changeautomatically to G8,G9,G10.... Let me know if that's what you're looking for. "CAT" wrote: Hi everyone, hope someone out there can help me with this one, abit long! I'm a fairly new user and using Excell 2007. Outside the main body of my worksheet I have set a LookUp table,with 2 columns starting with cell AM1 and finishing with cell AN29, withprecise values that will be needed in the spreadsheet: Col AM Col An 20 10 18 9 16 8.50 14 7.50 12 6.50 11 6.00 10 5.50 .... .... etc. etc., to 2.00 1.25 1.88 1.20 1.75 1.00 1.63 0.91 1.50 0,83 in cell An29, this being the last value of theseries. (A total of 29 values that are not all listed here). In my worksheet, the two columns involved a Col G - Manual data entry Entering randomly one value per cell out of the list in col AM of the LookUp table (or none) Col R - Calculated data Returning the equivalent value in col AN of the LookUp table (ornone) *What I do - Row 7 : First entry In col R, cell R7, I enter the following function: =VLOOKUP(8,$AM$1:$AN$29,2,FALSE) (the figure "8" being temporary) and copy it right down the column (up to 400 entries) Then, for each new entry that I enter (manually) in each cell ofcol G, I modify the formula, in the same row in col R, to match my entry, inother words I delete the "8" and replace it with the figure I have justtyped in: Example: Cell G7 : type in 16, Cell R7 : delete the 8 and replace by 16 to read: =VLOOKUP(16,$AM$1:$AN$29,2,FALSE) (which will give me the value 8.50 - col N of LookUp table) and so on (for up to 400 cells) until I have completed the entries;in the case of any blank cell in col G, I delete the formula in col R. The whole process is really time consuming and I make quite a fewerrors. *What I would like to achieve: Have a function (in column R?) that would automatically return, inthe equivalent cell in col R, the relevant value that is listed in thecolumn AN of the LookUp table, everytime a new entry is inserted in any ofthe cells of column G, but giving a blank when there are no equivalents. Sorry this is so longwinded, but I wanted to present this asclearly as possible so as not to confuse the issue in order to get an answer! Thank you for any help, as it will be much appreciated |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Function returning multiple values in a separate table
Hi David,
Excellent! Works like a dream: no more N/As !! And thank you for the mini lesson too - very helpful and instructive and I am absorbing it all like a sponge! As for the typing comment, I know what you mean, but I pride myself at being an excellent typist and I thought that typing it and visually seeing it unfold would help me understanding what the function was meant to be doing! I am very grateful for your time and patience, have a very good day. Kind Regards Cat "David Biddulph" wrote: Well, you shouldn't be trying (however carefully) to type in a formula which is given here. There is always a likelihood of typing errors, so instead of reading and typing, just copy the formula from here and paste it into the formula bar for the relevant cell on your worksheet. Having said that, the problem in this case wasn't yours, as the formula given by the previous poster had invalid syntax. I wonder whether he had intended not to say =if(ISNA(VLOOKUP(G7,$AM$1:$AN$29,2,FALSE))) but =IF(ISNA(VLOOKUP(G7,$AM$1:$AN$29,2,FALSE)),"",VLOO KUP(G7,$AM$1:$AN$29,2,FALSE))When Excel tell you that the formula is invalid, it tries to help by puttingthe cursor where the error is, and Excel help for the function in questionwill tell you the required syntax (and usually give you examples and in a"Show Also" link will often show related functions).In this case your IF function has a condition [the first parameter of theIF], but it doesn't have the consequence if true [2nd parameter of the IF],nor does it have the consequence if false [which is the 3rd parameter of theIF, which is optional in the syntax, but probably a significant parameter inthis case as the 2nd parameter is presumably handling the error case and youneed to handle the non-error case too].David Biddulph"CAT" wrote in ... Hi VMohan, Thank you very much for taking the time. I have typed your formula in (several times, and carefully) but keepgetting "error in formula" by Excell. Unfortunately, I wouldn't know where theerror is as I am very much a newbie! For the record, what I am exactly getting in col R (with no data in col G) is as follows: (sign for number (don't have it on the keyboard), then attached to it:N/A. Does that make any sense? "vmohan1978" wrote: use =if(ISNA(VLOOKUP(G7,$AM$1:$AN$29,2,FALSE))) "CAT" wrote: Hi H2fcell, Thanks a lot; that works great, I am getting exactly what I was lookingfor and it's so simple to enter. Just one thing: If I leave any cell in the G column blank, I am gettinga //N/A in column R and I wanted it blank, I suppose I could just erasethe formula, since it does not happen that often and it does not seem to interfere with the working of it in the following cells. Comparing to what I had to do before,it's all so simple; thanks againfor your help, you've saved me a lot of time. "h2fcell" wrote: Hi, I think you want to change your VLOOKUP to include a reference tocolumn G Type below in R7: =VLOOKUP(G7,$AM$1:$AN$29,2,FALSE) You can then copy that down column R and the G7 will changeautomatically to G8,G9,G10.... Let me know if that's what you're looking for. "CAT" wrote: Hi everyone, hope someone out there can help me with this one, abit long! I'm a fairly new user and using Excell 2007. Outside the main body of my worksheet I have set a LookUp table,with 2 columns starting with cell AM1 and finishing with cell AN29, withprecise values that will be needed in the spreadsheet: Col AM Col An 20 10 18 9 16 8.50 14 7.50 12 6.50 11 6.00 10 5.50 .... .... etc. etc., to 2.00 1.25 1.88 1.20 1.75 1.00 1.63 0.91 1.50 0,83 in cell An29, this being the last value of theseries. (A total of 29 values that are not all listed here). In my worksheet, the two columns involved a Col G - Manual data entry Entering randomly one value per cell out of the list in col AM of the LookUp table (or none) Col R - Calculated data Returning the equivalent value in col AN of the LookUp table (ornone) *What I do - Row 7 : First entry In col R, cell R7, I enter the following function: =VLOOKUP(8,$AM$1:$AN$29,2,FALSE) (the figure "8" being temporary) and copy it right down the column (up to 400 entries) Then, for each new entry that I enter (manually) in each cell ofcol G, I modify the formula, in the same row in col R, to match my entry, inother words I delete the "8" and replace it with the figure I have justtyped in: Example: Cell G7 : type in 16, Cell R7 : delete the 8 and replace by 16 to read: =VLOOKUP(16,$AM$1:$AN$29,2,FALSE) (which will give me the value 8.50 - col N of LookUp table) and so on (for up to 400 cells) until I have completed the entries;in the case of any blank cell in col G, I delete the formula in col R. The whole process is really time consuming and I make quite a fewerrors. *What I would like to achieve: Have a function (in column R?) that would automatically return, inthe equivalent cell in col R, the relevant value that is listed in thecolumn AN of the LookUp table, everytime a new entry is inserted in any ofthe cells of column G, but giving a blank when there are no equivalents. Sorry this is so longwinded, but I wanted to present this asclearly as possible so as not to confuse the issue in order to get an answer! Thank you for any help, as it will be much appreciated |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Function returning multiple values in a separate table
I didn't have any lack of faith in your typing ability as such (so my
wording was probably badly chosen), but when reading a formula from the screen it is often difficult to see where there are and aren't spaces, or to distinguish them from non-breaking spaces or other such characters, and similarly it can be difficult to distinguish two single quotes from one double quote, and the back-quote character ` can add additional confusion. In some fonts it can also be easy to confuse I, l, 1, or |. Sadly, although things can often look similar, Excel isn't bright enough to know what was intended. :-( I do, however, understand your point that typing things out can help things to stick in the mind. Glad to hear that the solution worked for you. [Let's hope that it agrees with what the previous poster had intended!] .... and sorry that the formatting of the quoted part of the previous message seems to be getting garbled on some of my messages today. I hate computers! -- David Biddulph "CAT" wrote in message ... Hi David, Excellent! Works like a dream: no more N/As !! And thank you for the mini lesson too - very helpful and instructive and I am absorbing it all like a sponge! As for the typing comment, I know what you mean, but I pride myself at being an excellent typist and I thought that typing it and visually seeing it unfold would help me understanding what the function was meant to be doing! I am very grateful for your time and patience, have a very good day. Kind Regards Cat "David Biddulph" wrote: Well, you shouldn't be trying (however carefully) to type in a formula which is given here. There is always a likelihood of typing errors, so instead of reading and typing, just copy the formula from here and paste it into the formula bar for the relevant cell on your worksheet. Having said that, the problem in this case wasn't yours, as the formula given by the previous poster had invalid syntax. I wonder whether he had intended not to say =if(ISNA(VLOOKUP(G7,$AM$1:$AN$29,2,FALSE))) but =IF(ISNA(VLOOKUP(G7,$AM$1:$AN$29,2,FALSE)),"",VLOO KUP(G7,$AM$1:$AN$29,2,FALSE)) When Excel tell you that the formula is invalid, it tries to help by putting the cursor where the error is, and Excel help for the function in question will tell you the required syntax (and usually give you examples and in a "Show Also" link will often show related functions). In this case your IF function has a condition [the first parameter of the IF], but it doesn't have the consequence if true [2nd parameter of the IF], nor does it have the consequence if false [which is the 3rd parameter of the IF, which is optional in the syntax, but probably a significant parameter in this case as the 2nd parameter is presumably handling the error case and you need to handle the non-error case too]. -- David Biddulph "CAT" wrote in message ... Hi VMohan, Thank you very much for taking the time. I have typed your formula in (several times, and carefully) but keepgetting "error in formula" by Excell. Unfortunately, I wouldn't know where theerror is as I am very much a newbie! For the record, what I am exactly getting in col R (with no data in col G) is as follows: (sign for number (don't have it on the keyboard), then attached to it:N/A. Does that make any sense? "vmohan1978" wrote: use =if(ISNA(VLOOKUP(G7,$AM$1:$AN$29,2,FALSE))) "CAT" wrote: Hi H2fcell, Thanks a lot; that works great, I am getting exactly what I was lookingfor and it's so simple to enter. Just one thing: If I leave any cell in the G column blank, I am getting a //N/A in column R and I wanted it blank, I suppose I could just erase the formula, since it does not happen that often and it does not seem to interfere with the working of it in the following cells. Comparing to what I had to do before,it's all so simple; thanks againfor your help, you've saved me a lot of time. "h2fcell" wrote: Hi, I think you want to change your VLOOKUP to include a reference tocolumn G Type below in R7: =VLOOKUP(G7,$AM$1:$AN$29,2,FALSE) You can then copy that down column R and the G7 will changeautomatically to G8,G9,G10.... Let me know if that's what you're looking for. .... |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Function returning multiple values in a separate table
....and I love 'em! most probably because of my "wide-eyed" wonder coupled
with plenty of ignorance that lead me to believe they can do everything (I know they can't really!), but my spreadsheet task-time is diminishing by the minute thanks to all you nice and clever people who have taken the time to answer my longwinded and clumsy attempts at making myself understood and given me plenty of food for thoughts too! And no offence taken, David, that was by way of a joke: I understand that typing a text in Word at the speed of knots is widely different to typing long formulae in Excell; I get your point completely and thank you again for all the added info re Excell. I hope your computer does give you joy today :-) Kind Regards, Cat "David Biddulph" wrote: I didn't have any lack of faith in your typing ability as such (so my wording was probably badly chosen), but when reading a formula from the screen it is often difficult to see where there are and aren't spaces, or to distinguish them from non-breaking spaces or other such characters, and similarly it can be difficult to distinguish two single quotes from one double quote, and the back-quote character ` can add additional confusion. In some fonts it can also be easy to confuse I, l, 1, or |. Sadly, although things can often look similar, Excel isn't bright enough to know what was intended. :-( I do, however, understand your point that typing things out can help things to stick in the mind. Glad to hear that the solution worked for you. [Let's hope that it agrees with what the previous poster had intended!] .... and sorry that the formatting of the quoted part of the previous message seems to be getting garbled on some of my messages today. I hate computers! -- David Biddulph "CAT" wrote in message ... Hi David, Excellent! Works like a dream: no more N/As !! And thank you for the mini lesson too - very helpful and instructive and I am absorbing it all like a sponge! As for the typing comment, I know what you mean, but I pride myself at being an excellent typist and I thought that typing it and visually seeing it unfold would help me understanding what the function was meant to be doing! I am very grateful for your time and patience, have a very good day. Kind Regards Cat "David Biddulph" wrote: Well, you shouldn't be trying (however carefully) to type in a formula which is given here. There is always a likelihood of typing errors, so instead of reading and typing, just copy the formula from here and paste it into the formula bar for the relevant cell on your worksheet. Having said that, the problem in this case wasn't yours, as the formula given by the previous poster had invalid syntax. I wonder whether he had intended not to say =if(ISNA(VLOOKUP(G7,$AM$1:$AN$29,2,FALSE))) but =IF(ISNA(VLOOKUP(G7,$AM$1:$AN$29,2,FALSE)),"",VLOO KUP(G7,$AM$1:$AN$29,2,FALSE)) When Excel tell you that the formula is invalid, it tries to help by putting the cursor where the error is, and Excel help for the function in question will tell you the required syntax (and usually give you examples and in a "Show Also" link will often show related functions). In this case your IF function has a condition [the first parameter of the IF], but it doesn't have the consequence if true [2nd parameter of the IF], nor does it have the consequence if false [which is the 3rd parameter of the IF, which is optional in the syntax, but probably a significant parameter in this case as the 2nd parameter is presumably handling the error case and you need to handle the non-error case too]. -- David Biddulph "CAT" wrote in message ... Hi VMohan, Thank you very much for taking the time. I have typed your formula in (several times, and carefully) but keepgetting "error in formula" by Excell. Unfortunately, I wouldn't know where theerror is as I am very much a newbie! For the record, what I am exactly getting in col R (with no data in col G) is as follows: (sign for number (don't have it on the keyboard), then attached to it:N/A. Does that make any sense? "vmohan1978" wrote: use =if(ISNA(VLOOKUP(G7,$AM$1:$AN$29,2,FALSE))) "CAT" wrote: Hi H2fcell, Thanks a lot; that works great, I am getting exactly what I was lookingfor and it's so simple to enter. Just one thing: If I leave any cell in the G column blank, I am getting a //N/A in column R and I wanted it blank, I suppose I could just erase the formula, since it does not happen that often and it does not seem to interfere with the working of it in the following cells. Comparing to what I had to do before,it's all so simple; thanks againfor your help, you've saved me a lot of time. "h2fcell" wrote: Hi, I think you want to change your VLOOKUP to include a reference tocolumn G Type below in R7: =VLOOKUP(G7,$AM$1:$AN$29,2,FALSE) You can then copy that down column R and the G7 will changeautomatically to G8,G9,G10.... Let me know if that's what you're looking for. .... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
returning multiple cell values from a vlookup | Excel Worksheet Functions | |||
Vlookup - returning multiple vertical values | Excel Discussion (Misc queries) | |||
Using VLOOKUP for returning multiple values and summing them | Excel Worksheet Functions | |||
VLookup returning the sum of multiple values from one "code" | Excel Worksheet Functions | |||
vlookup returning multiple values | Excel Worksheet Functions |