Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup on multiple cells
=VLOOKUP(C4,'Named Fields'!AH3:AM116,6,FALSE)
Hi, and thanks in advance for your help. I have the above formula set in a workbook. I have sheet 1 with my main information. I have sheet 2 with all my directives. So, I have set up as follows: sheet 1 contains: cell c3= client cell c4= project The vlookup I have entered in cell c5 is to return a job number based on the project in cell c4 which is listed on sheet2. Problem is, I have multiple projects named the same. They're under different clients. I found a post that said I could use: =vlookup(C3&C4,'Named Fields'!AH3:AM116,6,FALSE) But that didn't work. Is there any way to have vlookup find what's in cell C3, then proceed to cell C4 to returns the results in the 6th column? Thanks, Debbie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup on multiple cells
Hi Debbie,
if on the column h you have figures, try to use sumproduct as sumproduct(--(c7:c20="client")*(d7:d20="project"),(h7:h20)) hope this helps regards from Brazil Marcelo "Debbie" escreveu: =VLOOKUP(C4,'Named Fields'!AH3:AM116,6,FALSE) Hi, and thanks in advance for your help. I have the above formula set in a workbook. I have sheet 1 with my main information. I have sheet 2 with all my directives. So, I have set up as follows: sheet 1 contains: cell c3= client cell c4= project The vlookup I have entered in cell c5 is to return a job number based on the project in cell c4 which is listed on sheet2. Problem is, I have multiple projects named the same. They're under different clients. I found a post that said I could use: =vlookup(C3&C4,'Named Fields'!AH3:AM116,6,FALSE) But that didn't work. Is there any way to have vlookup find what's in cell C3, then proceed to cell C4 to returns the results in the 6th column? Thanks, Debbie |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup on multiple cells
Nel post
*Marcelo* ha scritto: Hi Debbie, if on the column h you have figures, try to use sumproduct as sumproduct(--(c7:c20="client")*(d7:d20="project"),(h7:h20)) Hi Marcelo, In this case you don't need "--". You can use sumproduct function in may ways: sumproduct(--(c7:c20="client"),--(d7:d20="project"),(h7:h20)) or sumproduct((c7:c20="client")*(d7:d20="project"),(h 7:h20)) or also sumproduct((c7:c20="client")*(d7:d20="project")*(h 7:h20)) But last one could produce errors if (h7:h20) contain a single text entry. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup on multiple cells
Marcelo and Franz,
Thanks but these didn't help. Any other suggestions? "Franz Verga" wrote: Nel post *Marcelo* ha scritto: Hi Debbie, if on the column h you have figures, try to use sumproduct as sumproduct(--(c7:c20="client")*(d7:d20="project"),(h7:h20)) Hi Marcelo, In this case you don't need "--". You can use sumproduct function in may ways: sumproduct(--(c7:c20="client"),--(d7:d20="project"),(h7:h20)) or sumproduct((c7:c20="client")*(d7:d20="project"),(h 7:h20)) or also sumproduct((c7:c20="client")*(d7:d20="project")*(h 7:h20)) But last one could produce errors if (h7:h20) contain a single text entry. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup on multiple cells
Nel post
*Debbie* ha scritto: "Franz Verga" wrote: Nel post *Marcelo* ha scritto: Hi Debbie, if on the column h you have figures, try to use sumproduct as sumproduct(--(c7:c20="client")*(d7:d20="project"),(h7:h20)) Hi Marcelo, In this case you don't need "--". You can use sumproduct function in may ways: sumproduct(--(c7:c20="client"),--(d7:d20="project"),(h7:h20)) or sumproduct((c7:c20="client")*(d7:d20="project"),(h 7:h20)) or also sumproduct((c7:c20="client")*(d7:d20="project")*(h 7:h20)) But last one could produce errors if (h7:h20) contain a single text entry. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy Marcelo and Franz, Thanks but these didn't help. Any other suggestions? Why didn't help? What is the result? -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup on multiple cells
I got #Value when I used that code.
"Franz Verga" wrote: Nel post *Debbie* ha scritto: "Franz Verga" wrote: Nel post *Marcelo* ha scritto: Hi Debbie, if on the column h you have figures, try to use sumproduct as sumproduct(--(c7:c20="client")*(d7:d20="project"),(h7:h20)) Hi Marcelo, In this case you don't need "--". You can use sumproduct function in may ways: sumproduct(--(c7:c20="client"),--(d7:d20="project"),(h7:h20)) or sumproduct((c7:c20="client")*(d7:d20="project"),(h 7:h20)) or also sumproduct((c7:c20="client")*(d7:d20="project")*(h 7:h20)) But last one could produce errors if (h7:h20) contain a single text entry. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy Marcelo and Franz, Thanks but these didn't help. Any other suggestions? Why didn't help? What is the result? -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup on multiple cells
Nel post
*Debbie* ha scritto: I got #Value when I used that code. Maybe it could be better if you post some data or if you could post a small example file on www.savefile.com "Franz Verga" wrote: Nel post *Debbie* ha scritto: "Franz Verga" wrote: Nel post *Marcelo* ha scritto: Hi Debbie, if on the column h you have figures, try to use sumproduct as sumproduct(--(c7:c20="client")*(d7:d20="project"),(h7:h20)) Hi Marcelo, In this case you don't need "--". You can use sumproduct function in may ways: sumproduct(--(c7:c20="client"),--(d7:d20="project"),(h7:h20)) or sumproduct((c7:c20="client")*(d7:d20="project"),(h 7:h20)) or also sumproduct((c7:c20="client")*(d7:d20="project")*(h 7:h20)) But last one could produce errors if (h7:h20) contain a single text entry. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy Marcelo and Franz, Thanks but these didn't help. Any other suggestions? Why didn't help? What is the result? -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy -- (I'm not sure of names of menus, options and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup on multiple cells
Nel post
*Debbie* ha scritto: Franz, I uploaded the file. PID 147340 Not sure what you need. Or you could e-mail me at Thanks for all your help. Debbie Hi Debbie, the number you posted doesn't permit to reach the file, you should post, instead the link to the file provided from www.savefile.com. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup on multiple cells
Franz,
I don't see a way to "post" my file. However, here's the full link. http://www.savefile.com/projects.php?pid=147340 Then it can be downloaded. If this doesn't work for you, please tell me how to "post" and I'll take care of it. Thanks, Debbie "Franz Verga" wrote: Nel post *Debbie* ha scritto: Franz, I uploaded the file. PID 147340 Not sure what you need. Or you could e-mail me at Thanks for all your help. Debbie Hi Debbie, the number you posted doesn't permit to reach the file, you should post, instead the link to the file provided from www.savefile.com. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup on multiple cells
Nel post
*Debbie* ha scritto: Franz, I don't see a way to "post" my file. I intended to upload... Sorry, English it's no my mother language, so sometimes I use the wrong words... However, here's the full link. http://www.savefile.com/projects.php?pid=147340 Then it can be downloaded. If this doesn't work for you, please tell me how to "post" and I'll take care of it. I fixed the file for you. To use a formula like =VLOOKUP(C3&C4,'Named Fields'!AH3:AM116,6,FALSE) also the first column of your table, i.e. column AH in the formula above, *must* be a concatenation of the two fields you are looking for. Here is the link were you can download your file modified: http://www.savefile.com/files/5666733 -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup on multiple cells
Nel post
*Franz Verga* ha scritto: Nel post *Debbie* ha scritto: Franz, I don't see a way to "post" my file. I intended to upload... Sorry, English it's no my mother language, so sometimes I use the wrong words... However, here's the full link. http://www.savefile.com/projects.php?pid=147340 Then it can be downloaded. If this doesn't work for you, please tell me how to "post" and I'll take care of it. I fixed the file for you. To use a formula like =VLOOKUP(C3&C4,'Named Fields'!AH3:AM116,6,FALSE) also the first column of your table, i.e. column AH in the formula above, *must* be a concatenation of the two fields you are looking for. Here is the link were you can download your file modified: http://www.savefile.com/files/5666733 I thik also you should "incapsulate" your VLOOKUP formulas inside an IF function, to prevent a display of #N/A errors: =IF(ISNA(VLOOKUP(....),"",VLOOKUP(...)) In this way way your VLOOKUP should return a #N/A, the cell will be blank. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup on multiple cells
Franz,
I've tried to download the file but it keeps kicking me to a "page not found". "Franz Verga" wrote: Nel post *Franz Verga* ha scritto: Nel post *Debbie* ha scritto: Franz, I don't see a way to "post" my file. I intended to upload... Sorry, English it's no my mother language, so sometimes I use the wrong words... However, here's the full link. http://www.savefile.com/projects.php?pid=147340 Then it can be downloaded. If this doesn't work for you, please tell me how to "post" and I'll take care of it. I fixed the file for you. To use a formula like =VLOOKUP(C3&C4,'Named Fields'!AH3:AM116,6,FALSE) also the first column of your table, i.e. column AH in the formula above, *must* be a concatenation of the two fields you are looking for. Here is the link were you can download your file modified: http://www.savefile.com/files/5666733 I thik also you should "incapsulate" your VLOOKUP formulas inside an IF function, to prevent a display of #N/A errors: =IF(ISNA(VLOOKUP(....),"",VLOOKUP(...)) In this way way your VLOOKUP should return a #N/A, the cell will be blank. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup on multiple cells
Nel post
*Debbie* ha scritto: Franz, I've tried to download the file but it keeps kicking me to a "page not found". I tried to upload the file again, but I receive an error of "page not found". I suspect some problem with the servers of site, so try to download the file later. Anyway, I explained before how to set your table to work with this kind of VLOOKUP formula: =VLOOKUP(C3&C4,'Named Fields'!AH3:AM116,6,FALSE) You have to set your table, here 'Named Fields'!AH3:AM116, with an extra column, so the table becomes 'Named Fields'!AG3:AN116; you have also to add 1 to your results column, here from 6 to 7, so the formula becomes: =VLOOKUP(C3&C4,'Named Fields'!AG3:AN116,7,FALSE) The new extra column *must* be a concatenation of the same fields, and in the same order, than C3 and C4. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup on multiple cells
Franz,
I did what you suggested. I placed the concatenate at the beginning. Works perfectly!!! Thank you so much for you time and patience. Debbie "Franz Verga" wrote: Nel post *Debbie* ha scritto: Franz, I've tried to download the file but it keeps kicking me to a "page not found". I tried to upload the file again, but I receive an error of "page not found". I suspect some problem with the servers of site, so try to download the file later. Anyway, I explained before how to set your table to work with this kind of VLOOKUP formula: =VLOOKUP(C3&C4,'Named Fields'!AH3:AM116,6,FALSE) You have to set your table, here 'Named Fields'!AH3:AM116, with an extra column, so the table becomes 'Named Fields'!AG3:AN116; you have also to add 1 to your results column, here from 6 to 7, so the formula becomes: =VLOOKUP(C3&C4,'Named Fields'!AG3:AN116,7,FALSE) The new extra column *must* be a concatenation of the same fields, and in the same order, than C3 and C4. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup on multiple cells
Nel post
*Debbie* ha scritto: Franz, I did what you suggested. I placed the concatenate at the beginning. Works perfectly!!! :-) Thank you so much for you time and patience. Debbie You're welcome. -- Ciao Franz Verga from Italy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using VLOOKUP with abitlity to choose from multiple defined names. | Excel Worksheet Functions | |||
VLOOKUP - Multiple cells in lookup value | Excel Worksheet Functions | |||
Count if cells have multiple data | Excel Worksheet Functions | |||
make multiple cells in 1 worksheet equal multiple cells in another | Excel Worksheet Functions | |||
Converting a link to values to multiple cells. | Excel Worksheet Functions |