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?

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?

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?

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?

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?

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?

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?