lookup formula

I am trying to come up with a formula that looks up an order number (eg
2013864) on a list on another sheet (2)and then returns all the listings
corresponding to that number eg:

Sheet 2
Order No.s Details
2013864 red
2013864 blue
2013864 green
2013865 red
2013865 green
2013864 black

Sheet 1
2013864 (this is where I enter number to be looked up)
red ( details that )
blue (correspond to)
green ( order No )


Regards
gregorK

The easiest way to go is by using AutoFilter or code.

It starts to get complicated when you want multiple returns by using
formulas.
Check out this old post:

http://tinyurl.com/2lakm
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"gregork" wrote in message
...
I am trying to come up with a formula that looks up an order number (eg
2013864) on a list on another sheet (2)and then returns all the listings
corresponding to that number eg:

Sheet 2
Order No.s Details
2013864 red
2013864 blue
2013864 green
2013865 red
2013865 green
2013864 black

Sheet 1
2013864 (this is where I enter number to be looked up)
red ( details that )
blue (correspond to)
green ( order No )


Regards
gregorK

"gregork" wrote...
I am trying to come up with a formula that looks up an order number
(eg 2013864) on a list on another sheet (2)and then returns all the
listings corresponding to that number eg:

Sheet 2
Order No.s Details
2013864 red
2013864 blue
2013864 green
2013865 red
2013865 green
2013864 black

Sheet 1
2013864 (this is where I enter number to be looked up)
red ( details that )
blue (correspond to)
gree ( order No )

You're missing black.

If the first table were named Data, and the order number entry cell named
Entry and in Sheet1!A1, try these *array* formulas.

Sheet1!A2:
=IF(COUNTIF(INDEX(Data,0,1),Entry),
INDEX(Data,MATCH(1,COUNTIF(Entry,INDEX(Data,0,1)), 0),2),"no entries")

Sheet1!A3:
=IF(COUNTIF(INDEX(Data,0,1),Entry)=ROW()-ROW(Entry),
INDEX(Data,MATCH(1,COUNTIF(Entry,INDEX(Data,0,1))
*(COUNTIF(A$2:A3,INDEX(Data,0,2))=0),0),2),"")

Select Sheet1!A3 and fill down as far as you believe is needed (safest to
fill down as many rows as are in Data).

Many thanks for the replies.
RD I agree that auto filter is the easiest way but other people will be
using my spreadsheet and I'm not sure if I want them filtering and messing
around with my sheet...its kind of like a database.....I would rather have
them type in a number in a dedicated cell (on a different sheet) and have
all the results displayed.
Harlan I have tried your formula and I end up with " #NAME? ". I think it is
probably due to the changes I had to make:

- Where you had "data" I put (Sheet2). Was I supposed to have: ('Sheet2'!)
or am I supposed to put the range of my list: ('Sheet2'!A2:A100). Also as my
list starts on cell A2 would this effect your formula..(Data,0,1) ?

Regards
gregorK

P.S. Sorry about missing the black.......seems to happen a lot......
especially when I'm playing pool.


"Harlan Grove" wrote in message
...
"gregork" wrote...
I am trying to come up with a formula that looks up an order number
(eg 2013864) on a list on another sheet (2)and then returns all the
listings corresponding to that number eg:

Sheet 2
Order No.s Details
2013864 red
2013864 blue
2013864 green
2013865 red
2013865 green
2013864 black

Sheet 1
2013864 (this is where I enter number to be looked up)
red ( details that )
blue (correspond to)
gree ( order No )

You're missing black.

If the first table were named Data, and the order number entry cell named
Entry and in Sheet1!A1, try these *array* formulas.

Sheet1!A2:
=IF(COUNTIF(INDEX(Data,0,1),Entry),
INDEX(Data,MATCH(1,COUNTIF(Entry,INDEX(Data,0,1)), 0),2),"no entries")

Sheet1!A3:
=IF(COUNTIF(INDEX(Data,0,1),Entry)=ROW()-ROW(Entry),
INDEX(Data,MATCH(1,COUNTIF(Entry,INDEX(Data,0,1))
*(COUNTIF(A$2:A3,INDEX(Data,0,2))=0),0),2),"")

Select Sheet1!A3 and fill down as far as you believe is needed (safest to
fill down as many rows as are in Data).

"I would rather have them type in a number in a dedicated cell (on a
different sheet) and have all the results displayed."

SO ... didn't you check out the link to the old post with a formula
solution?
The formula in that old post fits your scenario almost *exactly*!
All that you have to change is the sheet designation in the cell addresses,
and perhaps the size of the ranges and the lookup cell.

READ the post, and then try this.

YOUR Sheet 2:
A1 - Label - "Order Num."
B1 - Label - "Details"
A2:B100 = All your data

YOUR Sheet 1:
A1 - Label - "Order Num."
A2 - Enter the order number you wish to look up here.
A3 - Enter THIS formula:

=INDEX(Sheet2!B2:B100,SMALL(IF(Sheet2!A2:A100=A2,R OW(Sheet2!A2:A100)-1,""),R
OW(Sheet2!A2:A100)-1))

Don't worry if you get a #VALUE! error.

NOW, drag down to copy to A10.

While the cells are *still* selected,
Hit F2,
Then CSE (Ctrl Shift Enter).
This will *automatically* enclose the formulas in curly brackets { }.

Your formula cells should display a zero until you enter an order number in
A2 that exists in your data list on Sheet2.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"gregork" wrote in message
...
Many thanks for the replies.
RD I agree that auto filter is the easiest way but other people will be
using my spreadsheet and I'm not sure if I want them filtering and messing
around with my sheet...its kind of like a database.....I would rather have
them type in a number in a dedicated cell (on a different sheet) and have
all the results displayed.
Harlan I have tried your formula and I end up with " #NAME? ". I think it
is
probably due to the changes I had to make:

- Where you had "data" I put (Sheet2). Was I supposed to have: ('Sheet2'!)
or am I supposed to put the range of my list: ('Sheet2'!A2:A100). Also as
my
list starts on cell A2 would this effect your formula..(Data,0,1) ?

Regards
gregorK

P.S. Sorry about missing the black.......seems to happen a lot......
especially when I'm playing pool.


"Harlan Grove" wrote in message
...
"gregork" wrote...
I am trying to come up with a formula that looks up an order number
(eg 2013864) on a list on another sheet (2)and then returns all the
listings corresponding to that number eg:

Sheet 2
Order No.s Details
2013864 red
2013864 blue
2013864 green
2013865 red
2013865 green
2013864 black

Sheet 1
2013864 (this is where I enter number to be looked up)
red ( details that )
blue (correspond to)
gree ( order No )

You're missing black.

If the first table were named Data, and the order number entry cell
named
Entry and in Sheet1!A1, try these *array* formulas.

Sheet1!A2:
=IF(COUNTIF(INDEX(Data,0,1),Entry),
INDEX(Data,MATCH(1,COUNTIF(Entry,INDEX(Data,0,1)), 0),2),"no entries")

Sheet1!A3:
=IF(COUNTIF(INDEX(Data,0,1),Entry)=ROW()-ROW(Entry),
INDEX(Data,MATCH(1,COUNTIF(Entry,INDEX(Data,0,1))
*(COUNTIF(A$2:A3,INDEX(Data,0,2))=0),0),2),"")

Select Sheet1!A3 and fill down as far as you believe is needed (safest
to
fill down as many rows as are in Data).

"gregork" wrote...
....
Harlan I have tried your formula and I end up with " #NAME? ".
I think it is probably due to the changes I had to make:

- Where you had "data" I put (Sheet2). Was I supposed to have:
('Sheet2'!) or am I supposed to put the range of my list:
('Sheet2'!A2:A100). Also as my list starts on cell A2 would
this effect your formula..(Data,0,1) ?
....

Either give your *ENTIRE* original data range the defined name Data or
replace Data in my formula with the *complete* range address, indluding
worksheet name, of your original data range.

The original data range in your first post made it appear that there were
two columns, so wouldn't the range address be Sheet2!A2:A100? Also, unless
the column heading could match field values in the rows below it doesn't
matter whether the range starts with the row of column headers or the row
immediately below it.

"Harlan Grove" wrote...
....
The original data range in your first post made it appear that there
were two columns, so wouldn't the range address be Sheet2!A2:A100?
....

@#$%

Make that "wouldn't the range address be Sheet2!A2:B100?"

Thanks guys I've got it working well now. My apologies RD the link you gave
me originally did fit my needs exactly.

Kind Regards
gregorK

"gregork" wrote in message
...
Many thanks for the replies.
RD I agree that auto filter is the easiest way but other people will be
using my spreadsheet and I'm not sure if I want them filtering and messing
around with my sheet...its kind of like a database.....I would rather have
them type in a number in a dedicated cell (on a different sheet) and have
all the results displayed.
Harlan I have tried your formula and I end up with " #NAME? ". I think it
is
probably due to the changes I had to make:

- Where you had "data" I put (Sheet2). Was I supposed to have: ('Sheet2'!)
or am I supposed to put the range of my list: ('Sheet2'!A2:A100). Also as
my
list starts on cell A2 would this effect your formula..(Data,0,1) ?

Regards
gregorK

P.S. Sorry about missing the black.......seems to happen a lot......
especially when I'm playing pool.


"Harlan Grove" wrote in message
...
"gregork" wrote...
I am trying to come up with a formula that looks up an order number
(eg 2013864) on a list on another sheet (2)and then returns all the
listings corresponding to that number eg:

Sheet 2
Order No.s Details
2013864 red
2013864 blue
2013864 green
2013865 red
2013865 green
2013864 black

Sheet 1
2013864 (this is where I enter number to be looked up)
red ( details that )
blue (correspond to)
gree ( order No )

You're missing black.

If the first table were named Data, and the order number entry cell
named
Entry and in Sheet1!A1, try these *array* formulas.

Sheet1!A2:
=IF(COUNTIF(INDEX(Data,0,1),Entry),
INDEX(Data,MATCH(1,COUNTIF(Entry,INDEX(Data,0,1)), 0),2),"no entries")

Sheet1!A3:
=IF(COUNTIF(INDEX(Data,0,1),Entry)=ROW()-ROW(Entry),
INDEX(Data,MATCH(1,COUNTIF(Entry,INDEX(Data,0,1))
*(COUNTIF(A$2:A3,INDEX(Data,0,2))=0),0),2),"")

Select Sheet1!A3 and fill down as far as you believe is needed (safest
to
fill down as many rows as are in Data).

Thanks for the feed-back.

I do however, miss Alan Beban's usual addendum to solutions of this type!

He must have taken the weekend off.bg
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"gregork" wrote in message
...
Thanks guys I've got it working well now. My apologies RD the link you gave
me originally did fit my needs exactly.

Kind Regards
gregorK

"gregork" wrote in message
...
Many thanks for the replies.
RD I agree that auto filter is the easiest way but other people will be
using my spreadsheet and I'm not sure if I want them filtering and messing
around with my sheet...its kind of like a database.....I would rather have
them type in a number in a dedicated cell (on a different sheet) and have
all the results displayed.
Harlan I have tried your formula and I end up with " #NAME? ". I think it
is
probably due to the changes I had to make:

- Where you had "data" I put (Sheet2). Was I supposed to have: ('Sheet2'!)
or am I supposed to put the range of my list: ('Sheet2'!A2:A100). Also as
my
list starts on cell A2 would this effect your formula..(Data,0,1) ?

Regards
gregorK

P.S. Sorry about missing the black.......seems to happen a lot......
especially when I'm playing pool.


"Harlan Grove" wrote in message
...
"gregork" wrote...
I am trying to come up with a formula that looks up an order number
(eg 2013864) on a list on another sheet (2)and then returns all the
listings corresponding to that number eg:

Sheet 2
Order No.s Details
2013864 red
2013864 blue
2013864 green
2013865 red
2013865 green
2013864 black

Sheet 1
2013864 (this is where I enter number to be looked up)
red ( details that )
blue (correspond to)
gree ( order No )

You're missing black.

If the first table were named Data, and the order number entry cell
named
Entry and in Sheet1!A1, try these *array* formulas.

Sheet1!A2:
=IF(COUNTIF(INDEX(Data,0,1),Entry),
INDEX(Data,MATCH(1,COUNTIF(Entry,INDEX(Data,0,1)), 0),2),"no entries")

Sheet1!A3:
=IF(COUNTIF(INDEX(Data,0,1),Entry)=ROW()-ROW(Entry),
INDEX(Data,MATCH(1,COUNTIF(Entry,INDEX(Data,0,1))
*(COUNTIF(A$2:A3,INDEX(Data,0,2))=0),0),2),"")

Select Sheet1!A3 and fill down as far as you believe is needed (safest
to
fill down as many rows as are in Data).