Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup formula
I am looking to see if excel can help with a form of query.
Sheet2 contains various data similar to:- Code Description value (basicly a stock list) Sheet1 has a Cell (A1) where I type any part of code or description and any matching results from Sheet2 appear in rows A3 to A1000 I have failed upto now in figuring this one out - any help? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup formula
You want to lookup and get multiple results. Kind of like Autofilter but with
a formula. This is well described in: http://office.microsoft.com/en-us/ex...260381033.aspx -- Gary''s Student - gsnu200851 "CP" wrote: I am looking to see if excel can help with a form of query. Sheet2 contains various data similar to:- Code Description value (basicly a stock list) Sheet1 has a Cell (A1) where I type any part of code or description and any matching results from Sheet2 appear in rows A3 to A1000 I have failed upto now in figuring this one out - any help? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup formula
Ok tried to follow that however it doesnt work from the second part onwards
=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1) ) that just returns "VALUE" and everything else is the same from that point forward "Gary''s Student" wrote: You want to lookup and get multiple results. Kind of like Autofilter but with a formula. This is well described in: http://office.microsoft.com/en-us/ex...260381033.aspx -- Gary''s Student - gsnu200851 "CP" wrote: I am looking to see if excel can help with a form of query. Sheet2 contains various data similar to:- Code Description value (basicly a stock list) Sheet1 has a Cell (A1) where I type any part of code or description and any matching results from Sheet2 appear in rows A3 to A1000 I have failed upto now in figuring this one out - any help? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup formula
1. make sure you have adjusted the $A$1:$A7, etc. to match your needs
2. be sure to enter the formula with CNTRL-SHFT-ENTER and not just the ENTER key -- Gary''s Student - gsnu200851 "CP" wrote: Ok tried to follow that however it doesnt work from the second part onwards =SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1) ) that just returns "VALUE" and everything else is the same from that point forward "Gary''s Student" wrote: You want to lookup and get multiple results. Kind of like Autofilter but with a formula. This is well described in: http://office.microsoft.com/en-us/ex...260381033.aspx -- Gary''s Student - gsnu200851 "CP" wrote: I am looking to see if excel can help with a form of query. Sheet2 contains various data similar to:- Code Description value (basicly a stock list) Sheet1 has a Cell (A1) where I type any part of code or description and any matching results from Sheet2 appear in rows A3 to A1000 I have failed upto now in figuring this one out - any help? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup formula
Stil struggling to be honest
"1. make sure you have adjusted the $A$1:$A7, etc. to match your needs" I copied the one on the help page you gave me so I could learn from that "2. be sure to enter the formula with CNTRL-SHFT-ENTER and not just the ENTER key" when I CNTRL-SHIFT-ENTER nothing actually happens - I have tried pressing the function key (fx) and processing it that way but still no joy (the CNTRL-SHIFT-ENTER is something I have never heard of doing) thanks for your help "Gary''s Student" wrote: 1. make sure you have adjusted the $A$1:$A7, etc. to match your needs 2. be sure to enter the formula with CNTRL-SHFT-ENTER and not just the ENTER key -- Gary''s Student - gsnu200851 "CP" wrote: Ok tried to follow that however it doesnt work from the second part onwards =SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1) ) that just returns "VALUE" and everything else is the same from that point forward "Gary''s Student" wrote: You want to lookup and get multiple results. Kind of like Autofilter but with a formula. This is well described in: http://office.microsoft.com/en-us/ex...260381033.aspx -- Gary''s Student - gsnu200851 "CP" wrote: I am looking to see if excel can help with a form of query. Sheet2 contains various data similar to:- Code Description value (basicly a stock list) Sheet1 has a Cell (A1) where I type any part of code or description and any matching results from Sheet2 appear in rows A3 to A1000 I have failed upto now in figuring this one out - any help? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup formula
If you select the cell and it appears in the formula bar like:
{=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 ))} then the key strokes have been entered correctly If not, then: 1. click in the cell 2. click in the formula bar itself 3. touch CNTRL-SHFT-ENTER -- Gary''s Student - gsnu200851 "CP" wrote: Stil struggling to be honest "1. make sure you have adjusted the $A$1:$A7, etc. to match your needs" I copied the one on the help page you gave me so I could learn from that "2. be sure to enter the formula with CNTRL-SHFT-ENTER and not just the ENTER key" when I CNTRL-SHIFT-ENTER nothing actually happens - I have tried pressing the function key (fx) and processing it that way but still no joy (the CNTRL-SHIFT-ENTER is something I have never heard of doing) thanks for your help "Gary''s Student" wrote: 1. make sure you have adjusted the $A$1:$A7, etc. to match your needs 2. be sure to enter the formula with CNTRL-SHFT-ENTER and not just the ENTER key -- Gary''s Student - gsnu200851 "CP" wrote: Ok tried to follow that however it doesnt work from the second part onwards =SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1) ) that just returns "VALUE" and everything else is the same from that point forward "Gary''s Student" wrote: You want to lookup and get multiple results. Kind of like Autofilter but with a formula. This is well described in: http://office.microsoft.com/en-us/ex...260381033.aspx -- Gary''s Student - gsnu200851 "CP" wrote: I am looking to see if excel can help with a form of query. Sheet2 contains various data similar to:- Code Description value (basicly a stock list) Sheet1 has a Cell (A1) where I type any part of code or description and any matching results from Sheet2 appear in rows A3 to A1000 I have failed upto now in figuring this one out - any help? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup formula
Ok I was struggling with a little part
1. click in the cell 2. click in the formula bar itself type in formula now! 3. touch CNTRL-SHFT-ENTER but I have got it now thanks for you help "Gary''s Student" wrote: If you select the cell and it appears in the formula bar like: {=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 ))} then the key strokes have been entered correctly If not, then: 1. click in the cell 2. click in the formula bar itself 3. touch CNTRL-SHFT-ENTER -- Gary''s Student - gsnu200851 "CP" wrote: Stil struggling to be honest "1. make sure you have adjusted the $A$1:$A7, etc. to match your needs" I copied the one on the help page you gave me so I could learn from that "2. be sure to enter the formula with CNTRL-SHFT-ENTER and not just the ENTER key" when I CNTRL-SHIFT-ENTER nothing actually happens - I have tried pressing the function key (fx) and processing it that way but still no joy (the CNTRL-SHIFT-ENTER is something I have never heard of doing) thanks for your help "Gary''s Student" wrote: 1. make sure you have adjusted the $A$1:$A7, etc. to match your needs 2. be sure to enter the formula with CNTRL-SHFT-ENTER and not just the ENTER key -- Gary''s Student - gsnu200851 "CP" wrote: Ok tried to follow that however it doesnt work from the second part onwards =SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1) ) that just returns "VALUE" and everything else is the same from that point forward "Gary''s Student" wrote: You want to lookup and get multiple results. Kind of like Autofilter but with a formula. This is well described in: http://office.microsoft.com/en-us/ex...260381033.aspx -- Gary''s Student - gsnu200851 "CP" wrote: I am looking to see if excel can help with a form of query. Sheet2 contains various data similar to:- Code Description value (basicly a stock list) Sheet1 has a Cell (A1) where I type any part of code or description and any matching results from Sheet2 appear in rows A3 to A1000 I have failed upto now in figuring this one out - any help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup formula help | Excel Worksheet Functions | |||
Lookup formula? | Excel Discussion (Misc queries) | |||
Lookup Formula??? | Excel Worksheet Functions | |||
Using a Lookup Formula | Excel Discussion (Misc queries) | |||
Lookup Formula - but have a formula if it can't find/match a value | Excel Worksheet Functions |