Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Embarrassing question about lookup.
I am attempting to create what should be a simple formula. I am sure there
are better ways to do this but I am somewhat new so just going for what I know how to use. I have attempted VLOOKUP, but I get a bit confused on this. D5 is a drop down menu which is the name. (This Cell never moves). c5,c6,c7, etc€¦ is a numerical value I would like my new target cell to show. I only have 27 names. Heres my formula: =LOOKUP(D5,{"name1","name2","name3","name4","name5 ","name6","name7","name8","name9","name10","name11 ","name12","name13","name14","name15","name16","na me17","name18","name19","name20","name21","name22" ,"name23","name24","name25","name26","name27"},{"c 5","c6","c7","c8","c9","c10","c11","c12","c13","c1 4","c15","c16","c17","c18","c19","c20","c21","c22" ,"c23","c24","c25","c26","c27","c28","c29","c30"," c31"}) I think this formula is perfect except instead of showing €śc5€ť, etc€¦ I want it show the value of C5. Thanks in Advance |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Embarrassing question about lookup.
This should work. It is a formula array so you have to type Shft-Cntl Enter
to get the square brackets {=OFFSET(C5:C31,MATCH(TRUE,("name"&ROW(A1:A27)=D5) ,0)-1,0)} The line below produces an array {"name1","name2,...,"name27"} ("name"&ROW(A1:A27) The Match returns the index number of the item that matches D5 Then Offset get the item in C5:C31 using the index returned in the Match function. "Emil" wrote: I am attempting to create what should be a simple formula. I am sure there are better ways to do this but I am somewhat new so just going for what I know how to use. I have attempted VLOOKUP, but I get a bit confused on this. D5 is a drop down menu which is the name. (This Cell never moves). c5,c6,c7, etc€¦ is a numerical value I would like my new target cell to show. I only have 27 names. Heres my formula: =LOOKUP(D5,{"name1","name2","name3","name4","name5 ","name6","name7","name8","name9","name10","name11 ","name12","name13","name14","name15","name16","na me17","name18","name19","name20","name21","name22" ,"name23","name24","name25","name26","name27"},{"c 5","c6","c7","c8","c9","c10","c11","c12","c13","c1 4","c15","c16","c17","c18","c19","c20","c21","c22" ,"c23","c24","c25","c26","c27","c28","c29","c30"," c31"}) I think this formula is perfect except instead of showing €śc5€ť, etc€¦ I want it show the value of C5. Thanks in Advance |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Embarrassing question about lookup.
=LOOKUP(D5,{"name1","name2","name3"},C5:C7)
Extend it to 27 names! But I suggest to store the 27 names in a cell range, say B5:B31, then the formula is: =LOOKUP(D5,B5:B31,C5:C31) Regards, Stefi €žEmil€ť ezt Ă*rta: I am attempting to create what should be a simple formula. I am sure there are better ways to do this but I am somewhat new so just going for what I know how to use. I have attempted VLOOKUP, but I get a bit confused on this. D5 is a drop down menu which is the name. (This Cell never moves). c5,c6,c7, etc€¦ is a numerical value I would like my new target cell to show. I only have 27 names. Heres my formula: =LOOKUP(D5,{"name1","name2","name3","name4","name5 ","name6","name7","name8","name9","name10","name11 ","name12","name13","name14","name15","name16","na me17","name18","name19","name20","name21","name22" ,"name23","name24","name25","name26","name27"},{"c 5","c6","c7","c8","c9","c10","c11","c12","c13","c1 4","c15","c16","c17","c18","c19","c20","c21","c22" ,"c23","c24","c25","c26","c27","c28","c29","c30"," c31"}) I think this formula is perfect except instead of showing €śc5€ť, etc€¦ I want it show the value of C5. Thanks in Advance |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Embarrassing question about lookup.
Stefi:
Wow, that was soo easy! Worked liked a Charm** :) I can't thank you enough - I have literally spent hours attempting to figure this out! Also thank you Joel €“ I have a lot to learn and you have given me some direction on what other functions I should know. "Stefi" wrote: =LOOKUP(D5,{"name1","name2","name3"},C5:C7) Extend it to 27 names! But I suggest to store the 27 names in a cell range, say B5:B31, then the formula is: =LOOKUP(D5,B5:B31,C5:C31) Regards, Stefi €žEmil€ť ezt Ă*rta: I am attempting to create what should be a simple formula. I am sure there are better ways to do this but I am somewhat new so just going for what I know how to use. I have attempted VLOOKUP, but I get a bit confused on this. D5 is a drop down menu which is the name. (This Cell never moves). c5,c6,c7, etc€¦ is a numerical value I would like my new target cell to show. I only have 27 names. Heres my formula: =LOOKUP(D5,{"name1","name2","name3","name4","name5 ","name6","name7","name8","name9","name10","name11 ","name12","name13","name14","name15","name16","na me17","name18","name19","name20","name21","name22" ,"name23","name24","name25","name26","name27"},{"c 5","c6","c7","c8","c9","c10","c11","c12","c13","c1 4","c15","c16","c17","c18","c19","c20","c21","c22" ,"c23","c24","c25","c26","c27","c28","c29","c30"," c31"}) I think this formula is perfect except instead of showing €śc5€ť, etc€¦ I want it show the value of C5. Thanks in Advance |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Embarrassing question about lookup.
You are welcome! Thanks for the feedback!
Stefi €žEmil€ť ezt Ă*rta: Stefi: Wow, that was soo easy! Worked liked a Charm** :) I can't thank you enough - I have literally spent hours attempting to figure this out! Also thank you Joel €“ I have a lot to learn and you have given me some direction on what other functions I should know. "Stefi" wrote: =LOOKUP(D5,{"name1","name2","name3"},C5:C7) Extend it to 27 names! But I suggest to store the 27 names in a cell range, say B5:B31, then the formula is: =LOOKUP(D5,B5:B31,C5:C31) Regards, Stefi €žEmil€ť ezt Ă*rta: I am attempting to create what should be a simple formula. I am sure there are better ways to do this but I am somewhat new so just going for what I know how to use. I have attempted VLOOKUP, but I get a bit confused on this. D5 is a drop down menu which is the name. (This Cell never moves). c5,c6,c7, etc€¦ is a numerical value I would like my new target cell to show. I only have 27 names. Heres my formula: =LOOKUP(D5,{"name1","name2","name3","name4","name5 ","name6","name7","name8","name9","name10","name11 ","name12","name13","name14","name15","name16","na me17","name18","name19","name20","name21","name22" ,"name23","name24","name25","name26","name27"},{"c 5","c6","c7","c8","c9","c10","c11","c12","c13","c1 4","c15","c16","c17","c18","c19","c20","c21","c22" ,"c23","c24","c25","c26","c27","c28","c29","c30"," c31"}) I think this formula is perfect except instead of showing €śc5€ť, etc€¦ I want it show the value of C5. Thanks in Advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another Lookup Question | Excel Discussion (Misc queries) | |||
LOOKUP Question [again!] | Excel Discussion (Misc queries) | |||
LOOKUP Question | Excel Discussion (Misc queries) | |||
LOOKUP question | Excel Worksheet Functions | |||
Lookup Question | Excel Worksheet Functions |