VLOOKUP

[Daniell]

What I am looking for is a way to locate supplies. I have been told that a
VLOOKUP would work but I cannot seem to come up with the proper syntax. What
I am looking for is to have a dialog box that I would enter the STORE DESC
and have it list all of the items that come under that column with the ROW,
SELF, BRAND DESC.



Row Shelf Store Desc Supplier Desc Brand Dec Cost Lead/Days
A 3 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
D 5 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
V 4 Cleaner CLR425 Ultra Hand Cleaner 1.23 3
A 4 Bags HB564 Rich Paper Bags 0.23 2
G 8 Bags HB564 Rich Paper Bags 0.23 2
V 9 Bags HB564 Rich Paper Bags 0.23 2
D 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
G 5 Rubber Bands RB897 Secure Rubber Bands 0.1 2
S 3 Rubber Bands RB897 Secure Rubber Bands 0.1 2
S 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
Thanks in advance for the help.

[Dave Peterson]

=Vlookup() returns a single value--not a list.

I'd select the whole range and do data|filter|autofilter.

Then I could use the dropdown arrow in the "store desc" field and choose to see
the item that I want. (There's lots of things you can do with that filter!)

Then data|filter|show all to see everything.

Daniell wrote:
>
> What I am looking for is a way to locate supplies. I have been told that a
> VLOOKUP would work but I cannot seem to come up with the proper syntax. What
> I am looking for is to have a dialog box that I would enter the STORE DESC
> and have it list all of the items that come under that column with the ROW,
> SELF, BRAND DESC.
>
> Row Shelf Store Desc Supplier Desc Brand Dec Cost Lead/Days
> A 3 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> D 5 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> V 4 Cleaner CLR425 Ultra Hand Cleaner 1.23 3
> A 4 Bags HB564 Rich Paper Bags 0.23 2
> G 8 Bags HB564 Rich Paper Bags 0.23 2
> V 9 Bags HB564 Rich Paper Bags 0.23 2
> D 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> G 5 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 3 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> Thanks in advance for the help.

--

Dave Peterson

[Excelenator]

Is your data in columns as you have laid it out? If so why not use the Data\Filter\AutoFilter and then you can use the drop down list in the column header to "locate" the Store Desc you want and only those rows will appear that have the Store Desc you selected.

I might be missing something but this seems like it would work for you.

[shail]

hi Daniell,

Dave & Excellenator has given you good suggestions to use Autofilter.

And to VLOOKUP you need to have at least one column unique so that
VLOOKUP can work normal. Moreover, the STORE DESC must be at the first
column.

Try rearranging your table if possible.

Thanks

Shail


Daniell wrote:
> What I am looking for is a way to locate supplies. I have been told that a
> VLOOKUP would work but I cannot seem to come up with the proper syntax. What
> I am looking for is to have a dialog box that I would enter the STORE DESC
> and have it list all of the items that come under that column with the ROW,
> SELF, BRAND DESC.
>
>
>
> Row Shelf Store Desc Supplier Desc Brand Dec Cost Lead/Days
> A 3 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> D 5 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> V 4 Cleaner CLR425 Ultra Hand Cleaner 1.23 3
> A 4 Bags HB564 Rich Paper Bags 0.23 2
> G 8 Bags HB564 Rich Paper Bags 0.23 2
> V 9 Bags HB564 Rich Paper Bags 0.23 2
> D 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> G 5 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 3 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> Thanks in advance for the help.

[shail]

hi Daniell,

Dave & Excellenator has given you good suggestions to use Autofilter.

And to VLOOKUP you need to have at least one column unique so that
VLOOKUP can work normal. Moreover, the STORE DESC must be at the first
column.

Try rearranging your table if possible.

Thanks

Shail


Daniell wrote:
> What I am looking for is a way to locate supplies. I have been told that a
> VLOOKUP would work but I cannot seem to come up with the proper syntax. What
> I am looking for is to have a dialog box that I would enter the STORE DESC
> and have it list all of the items that come under that column with the ROW,
> SELF, BRAND DESC.
>
>
>
> Row Shelf Store Desc Supplier Desc Brand Dec Cost Lead/Days
> A 3 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> D 5 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> V 4 Cleaner CLR425 Ultra Hand Cleaner 1.23 3
> A 4 Bags HB564 Rich Paper Bags 0.23 2
> G 8 Bags HB564 Rich Paper Bags 0.23 2
> V 9 Bags HB564 Rich Paper Bags 0.23 2
> D 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> G 5 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 3 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> Thanks in advance for the help.

[shail]

hi Daniell,

Dave & Excellenator has given you good suggestions to use Autofilter.

And to VLOOKUP you need to have at least one column unique so that
VLOOKUP can work normal. Moreover, the STORE DESC must be at the first
column.

Try rearranging your table if possible.

Thanks

Shail


Daniell wrote:
> What I am looking for is a way to locate supplies. I have been told that a
> VLOOKUP would work but I cannot seem to come up with the proper syntax. What
> I am looking for is to have a dialog box that I would enter the STORE DESC
> and have it list all of the items that come under that column with the ROW,
> SELF, BRAND DESC.
>
>
>
> Row Shelf Store Desc Supplier Desc Brand Dec Cost Lead/Days
> A 3 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> D 5 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> V 4 Cleaner CLR425 Ultra Hand Cleaner 1.23 3
> A 4 Bags HB564 Rich Paper Bags 0.23 2
> G 8 Bags HB564 Rich Paper Bags 0.23 2
> V 9 Bags HB564 Rich Paper Bags 0.23 2
> D 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> G 5 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 3 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> Thanks in advance for the help.

[shail]

hi Daniell,

Dave & Excellenator has given you good suggestions to use Autofilter.

And to VLOOKUP you need to have at least one column unique so that
VLOOKUP can work normal. Moreover, the STORE DESC must be at the first
column.

Try rearranging your table if possible.

Thanks

Shail


Daniell wrote:
> What I am looking for is a way to locate supplies. I have been told that a
> VLOOKUP would work but I cannot seem to come up with the proper syntax. What
> I am looking for is to have a dialog box that I would enter the STORE DESC
> and have it list all of the items that come under that column with the ROW,
> SELF, BRAND DESC.
>
>
>
> Row Shelf Store Desc Supplier Desc Brand Dec Cost Lead/Days
> A 3 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> D 5 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> V 4 Cleaner CLR425 Ultra Hand Cleaner 1.23 3
> A 4 Bags HB564 Rich Paper Bags 0.23 2
> G 8 Bags HB564 Rich Paper Bags 0.23 2
> V 9 Bags HB564 Rich Paper Bags 0.23 2
> D 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> G 5 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 3 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> Thanks in advance for the help.

[shail]

hi Daniell,

Dave & Excellenator has given you good suggestions to use Autofilter.

And to VLOOKUP you need to have at least one column unique so that
VLOOKUP can work normal. Moreover, the STORE DESC must be at the first
column.

Try rearranging your table if possible.

Thanks

Shail


Daniell wrote:
> What I am looking for is a way to locate supplies. I have been told that a
> VLOOKUP would work but I cannot seem to come up with the proper syntax. What
> I am looking for is to have a dialog box that I would enter the STORE DESC
> and have it list all of the items that come under that column with the ROW,
> SELF, BRAND DESC.
>
>
>
> Row Shelf Store Desc Supplier Desc Brand Dec Cost Lead/Days
> A 3 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> D 5 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> V 4 Cleaner CLR425 Ultra Hand Cleaner 1.23 3
> A 4 Bags HB564 Rich Paper Bags 0.23 2
> G 8 Bags HB564 Rich Paper Bags 0.23 2
> V 9 Bags HB564 Rich Paper Bags 0.23 2
> D 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> G 5 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 3 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> Thanks in advance for the help.

[shail]

hi Daniell,

Dave & Excellenator has given you good suggestions to use Autofilter.

And to VLOOKUP you need to have at least one column unique so that
VLOOKUP can work normal. Moreover, the STORE DESC must be at the first
column.

Try rearranging your table if possible.

Thanks

Shail


Daniell wrote:
> What I am looking for is a way to locate supplies. I have been told that a
> VLOOKUP would work but I cannot seem to come up with the proper syntax. What
> I am looking for is to have a dialog box that I would enter the STORE DESC
> and have it list all of the items that come under that column with the ROW,
> SELF, BRAND DESC.
>
>
>
> Row Shelf Store Desc Supplier Desc Brand Dec Cost Lead/Days
> A 3 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> D 5 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> V 4 Cleaner CLR425 Ultra Hand Cleaner 1.23 3
> A 4 Bags HB564 Rich Paper Bags 0.23 2
> G 8 Bags HB564 Rich Paper Bags 0.23 2
> V 9 Bags HB564 Rich Paper Bags 0.23 2
> D 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> G 5 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 3 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> Thanks in advance for the help.

[shail]

hi Daniell,

Dave & Excellenator has given you good suggestions to use Autofilter.

And to VLOOKUP you need to have at least one column unique so that
VLOOKUP can work normal. Moreover, the STORE DESC must be at the first
column.

Try rearranging your table if possible.

Thanks

Shail


Daniell wrote:
> What I am looking for is a way to locate supplies. I have been told that a
> VLOOKUP would work but I cannot seem to come up with the proper syntax. What
> I am looking for is to have a dialog box that I would enter the STORE DESC
> and have it list all of the items that come under that column with the ROW,
> SELF, BRAND DESC.
>
>
>
> Row Shelf Store Desc Supplier Desc Brand Dec Cost Lead/Days
> A 3 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> D 5 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> V 4 Cleaner CLR425 Ultra Hand Cleaner 1.23 3
> A 4 Bags HB564 Rich Paper Bags 0.23 2
> G 8 Bags HB564 Rich Paper Bags 0.23 2
> V 9 Bags HB564 Rich Paper Bags 0.23 2
> D 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> G 5 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 3 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> Thanks in advance for the help.

[shail]

hi Daniell,

Dave & Excellenator has given you good suggestions to use Autofilter.

And to VLOOKUP you need to have at least one column unique so that
VLOOKUP can work normal. Moreover, the STORE DESC must be at the first
column.

Try rearranging your table if possible.

Thanks

Shail


Daniell wrote:
> What I am looking for is a way to locate supplies. I have been told that a
> VLOOKUP would work but I cannot seem to come up with the proper syntax. What
> I am looking for is to have a dialog box that I would enter the STORE DESC
> and have it list all of the items that come under that column with the ROW,
> SELF, BRAND DESC.
>
>
>
> Row Shelf Store Desc Supplier Desc Brand Dec Cost Lead/Days
> A 3 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> D 5 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> V 4 Cleaner CLR425 Ultra Hand Cleaner 1.23 3
> A 4 Bags HB564 Rich Paper Bags 0.23 2
> G 8 Bags HB564 Rich Paper Bags 0.23 2
> V 9 Bags HB564 Rich Paper Bags 0.23 2
> D 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> G 5 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 3 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> Thanks in advance for the help.

[shail]

hi Daniell,

Dave & Excellenator has given you good suggestions to use Autofilter.

And to VLOOKUP you need to have at least one column unique so that
VLOOKUP can work normal. Moreover, the STORE DESC must be at the first
column.

Try rearranging your table if possible.

Thanks

Shail


Daniell wrote:
> What I am looking for is a way to locate supplies. I have been told that a
> VLOOKUP would work but I cannot seem to come up with the proper syntax. What
> I am looking for is to have a dialog box that I would enter the STORE DESC
> and have it list all of the items that come under that column with the ROW,
> SELF, BRAND DESC.
>
>
>
> Row Shelf Store Desc Supplier Desc Brand Dec Cost Lead/Days
> A 3 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> D 5 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> V 4 Cleaner CLR425 Ultra Hand Cleaner 1.23 3
> A 4 Bags HB564 Rich Paper Bags 0.23 2
> G 8 Bags HB564 Rich Paper Bags 0.23 2
> V 9 Bags HB564 Rich Paper Bags 0.23 2
> D 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> G 5 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 3 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> Thanks in advance for the help.

[shail]

hi Daniell,

Dave & Excellenator has given you good suggestions to use Autofilter.

And to VLOOKUP you need to have at least one column unique so that
VLOOKUP can work normal. Moreover, the STORE DESC must be at the first
column.

Try rearranging your table if possible.

Thanks

Shail


Daniell wrote:
> What I am looking for is a way to locate supplies. I have been told that a
> VLOOKUP would work but I cannot seem to come up with the proper syntax. What
> I am looking for is to have a dialog box that I would enter the STORE DESC
> and have it list all of the items that come under that column with the ROW,
> SELF, BRAND DESC.
>
>
>
> Row Shelf Store Desc Supplier Desc Brand Dec Cost Lead/Days
> A 3 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> D 5 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> V 4 Cleaner CLR425 Ultra Hand Cleaner 1.23 3
> A 4 Bags HB564 Rich Paper Bags 0.23 2
> G 8 Bags HB564 Rich Paper Bags 0.23 2
> V 9 Bags HB564 Rich Paper Bags 0.23 2
> D 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> G 5 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 3 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> Thanks in advance for the help.

[Dave Peterson]

That column to match doesn't have to be the first column in the worksheet--it
just has to be the leftmost column in the lookup range.

If it isn't the leftmost column, then you could use =index(match()) (instead of
=vlookup()).

Debra Dalgleish has some notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))

And the data in the first column doesn't have to be unique--but with a formula
like:

=vlookup(a1,sheet2!a:e,4,false)

Only the data that is on the same row as the first match will be returned.



shail wrote:
>
> hi Daniell,
>
> Dave & Excellenator has given you good suggestions to use Autofilter.
>
> And to VLOOKUP you need to have at least one column unique so that
> VLOOKUP can work normal. Moreover, the STORE DESC must be at the first
> column.
>
> Try rearranging your table if possible.
>
> Thanks
>
> Shail
>
> Daniell wrote:
> > What I am looking for is a way to locate supplies. I have been told that a
> > VLOOKUP would work but I cannot seem to come up with the proper syntax. What
> > I am looking for is to have a dialog box that I would enter the STORE DESC
> > and have it list all of the items that come under that column with the ROW,
> > SELF, BRAND DESC.
> >
> >
> >
> > Row Shelf Store Desc Supplier Desc Brand Dec Cost Lead/Days
> > A 3 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> > D 5 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> > V 4 Cleaner CLR425 Ultra Hand Cleaner 1.23 3
> > A 4 Bags HB564 Rich Paper Bags 0.23 2
> > G 8 Bags HB564 Rich Paper Bags 0.23 2
> > V 9 Bags HB564 Rich Paper Bags 0.23 2
> > D 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> > G 5 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> > S 3 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> > S 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> > Thanks in advance for the help.

--

Dave Peterson

[Daniell]

Thanks Guys for all the help.

"shail" wrote:

> hi Daniell,
>
> Dave & Excellenator has given you good suggestions to use Autofilter.
>
> And to VLOOKUP you need to have at least one column unique so that
> VLOOKUP can work normal. Moreover, the STORE DESC must be at the first
> column.
>
> Try rearranging your table if possible.
>
> Thanks
>
> Shail
>
>
> Daniell wrote:
> > What I am looking for is a way to locate supplies. I have been told that a
> > VLOOKUP would work but I cannot seem to come up with the proper syntax. What
> > I am looking for is to have a dialog box that I would enter the STORE DESC
> > and have it list all of the items that come under that column with the ROW,
> > SELF, BRAND DESC.
> >
> >
> >
> > Row Shelf Store Desc Supplier Desc Brand Dec Cost Lead/Days
> > A 3 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> > D 5 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> > V 4 Cleaner CLR425 Ultra Hand Cleaner 1.23 3
> > A 4 Bags HB564 Rich Paper Bags 0.23 2
> > G 8 Bags HB564 Rich Paper Bags 0.23 2
> > V 9 Bags HB564 Rich Paper Bags 0.23 2
> > D 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> > G 5 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> > S 3 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> > S 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> > Thanks in advance for the help.
>
>