Lookup:budget management

[The Toaster]

I am creating a workbook that is part of budget management. Within the
workbook is a list of products (i.e: apples, pears, oranges) & their budget
code. People need to be able to add new codes all the time. This data is
called through to the main spreadsheet & listed in a hidden table. From here
to data is shown in validation pick lists & the appropriate code is pulled
using HLOOKUP from the hidden table.

Unfortunately due to the stupidity of the users we have to keep as much of
the workbook locked & restrict useage.

The problem is that when we have two items with the same starting letter
(i.e corn & carrotts) HLOOKUP is only picking up the first code it comes two
& not comparing the full text.......help!

[Toppers]

HLOOKUP works on the full text.

I tested a table with Corn & Carrots and it worked OK.

Have you included FALSE as the last parameter to find an EXACT Match? If
not, it will find the closest match, assuming data is sorted in ascending
order.

..
=HLOOKUP(D1,F1:J2,2,FALSE)

HTH

"The Toaster" wrote:

> I am creating a workbook that is part of budget management. Within the
> workbook is a list of products (i.e: apples, pears, oranges) & their budget
> code. People need to be able to add new codes all the time. This data is
> called through to the main spreadsheet & listed in a hidden table. From here
> to data is shown in validation pick lists & the appropriate code is pulled
> using HLOOKUP from the hidden table.
>
> Unfortunately due to the stupidity of the users we have to keep as much of
> the workbook locked & restrict useage.
>
> The problem is that when we have two items with the same starting letter
> (i.e corn & carrotts) HLOOKUP is only picking up the first code it comes two
> & not comparing the full text.......help!
>

[The Toaster]

=LOOKUP($H13,$I$35:$I$41,$G$35:$G$40)

This is the formula we are currently using. Tried your suggestion & couldn't
get it to work correctly.

The idea is that the user selects from the validation list & the appropriate
code is picked from the hidden list underneath.

Suggestions?


"Toppers" wrote:

> HLOOKUP works on the full text.
>
> I tested a table with Corn & Carrots and it worked OK.
>
> Have you included FALSE as the last parameter to find an EXACT Match? If
> not, it will find the closest match, assuming data is sorted in ascending
> order.
>
> .
> =HLOOKUP(D1,F1:J2,2,FALSE)
>
> HTH
>
> "The Toaster" wrote:
>
> > I am creating a workbook that is part of budget management. Within the
> > workbook is a list of products (i.e: apples, pears, oranges) & their budget
> > code. People need to be able to add new codes all the time. This data is
> > called through to the main spreadsheet & listed in a hidden table. From here
> > to data is shown in validation pick lists & the appropriate code is pulled
> > using HLOOKUP from the hidden table.
> >
> > Unfortunately due to the stupidity of the users we have to keep as much of
> > the workbook locked & restrict useage.
> >
> > The problem is that when we have two items with the same starting letter
> > (i.e corn & carrotts) HLOOKUP is only picking up the first code it comes two
> > & not comparing the full text.......help!
> >

[Toppers]

You originaaly said HLOOKUP, but now it's LOOKUP.

Testing worked for me using a validation list.

I am assuming a typo in your lookup (g40 should be g41)

And data must be in ascending order.

=LOOKUP($H13,$I$35:$I$41,$G$35:$G$41)



"The Toaster" wrote:

> =LOOKUP($H13,$I$35:$I$41,$G$35:$G$40)
>
> This is the formula we are currently using. Tried your suggestion & couldn't
> get it to work correctly.
>
> The idea is that the user selects from the validation list & the appropriate
> code is picked from the hidden list underneath.
>
> Suggestions?
>
>
> "Toppers" wrote:
>
> > HLOOKUP works on the full text.
> >
> > I tested a table with Corn & Carrots and it worked OK.
> >
> > Have you included FALSE as the last parameter to find an EXACT Match? If
> > not, it will find the closest match, assuming data is sorted in ascending
> > order.
> >
> > .
> > =HLOOKUP(D1,F1:J2,2,FALSE)
> >
> > HTH
> >
> > "The Toaster" wrote:
> >
> > > I am creating a workbook that is part of budget management. Within the
> > > workbook is a list of products (i.e: apples, pears, oranges) & their budget
> > > code. People need to be able to add new codes all the time. This data is
> > > called through to the main spreadsheet & listed in a hidden table. From here
> > > to data is shown in validation pick lists & the appropriate code is pulled
> > > using HLOOKUP from the hidden table.
> > >
> > > Unfortunately due to the stupidity of the users we have to keep as much of
> > > the workbook locked & restrict useage.
> > >
> > > The problem is that when we have two items with the same starting letter
> > > (i.e corn & carrotts) HLOOKUP is only picking up the first code it comes two
> > > & not comparing the full text.......help!
> > >

[The Toaster]

The ascending order seems to make a big difference.

Why does it not work above 19 rows though? If I increase the rows above 20
it blanks out?

ANy ideas?

"Toppers" wrote:

> You originaaly said HLOOKUP, but now it's LOOKUP.
>
> Testing worked for me using a validation list.
>
> I am assuming a typo in your lookup (g40 should be g41)
>
> And data must be in ascending order.
>
> =LOOKUP($H13,$I$35:$I$41,$G$35:$G$41)
>
>
>
> "The Toaster" wrote:
>
> > =LOOKUP($H13,$I$35:$I$41,$G$35:$G$40)
> >
> > This is the formula we are currently using. Tried your suggestion & couldn't
> > get it to work correctly.
> >
> > The idea is that the user selects from the validation list & the appropriate
> > code is picked from the hidden list underneath.
> >
> > Suggestions?
> >
> >
> > "Toppers" wrote:
> >
> > > HLOOKUP works on the full text.
> > >
> > > I tested a table with Corn & Carrots and it worked OK.
> > >
> > > Have you included FALSE as the last parameter to find an EXACT Match? If
> > > not, it will find the closest match, assuming data is sorted in ascending
> > > order.
> > >
> > > .
> > > =HLOOKUP(D1,F1:J2,2,FALSE)
> > >
> > > HTH
> > >
> > > "The Toaster" wrote:
> > >
> > > > I am creating a workbook that is part of budget management. Within the
> > > > workbook is a list of products (i.e: apples, pears, oranges) & their budget
> > > > code. People need to be able to add new codes all the time. This data is
> > > > called through to the main spreadsheet & listed in a hidden table. From here
> > > > to data is shown in validation pick lists & the appropriate code is pulled
> > > > using HLOOKUP from the hidden table.
> > > >
> > > > Unfortunately due to the stupidity of the users we have to keep as much of
> > > > the workbook locked & restrict useage.
> > > >
> > > > The problem is that when we have two items with the same starting letter
> > > > (i.e corn & carrotts) HLOOKUP is only picking up the first code it comes two
> > > > & not comparing the full text.......help!
> > > >

[Toppers]

There is no limit other than the 65000+ rows in Excel. So something else must
be wrong. What exactly do you mean by blank out?

"The Toaster" wrote:

> The ascending order seems to make a big difference.
>
> Why does it not work above 19 rows though? If I increase the rows above 20
> it blanks out?
>
> ANy ideas?
>
> "Toppers" wrote:
>
> > You originaaly said HLOOKUP, but now it's LOOKUP.
> >
> > Testing worked for me using a validation list.
> >
> > I am assuming a typo in your lookup (g40 should be g41)
> >
> > And data must be in ascending order.
> >
> > =LOOKUP($H13,$I$35:$I$41,$G$35:$G$41)
> >
> >
> >
> > "The Toaster" wrote:
> >
> > > =LOOKUP($H13,$I$35:$I$41,$G$35:$G$40)
> > >
> > > This is the formula we are currently using. Tried your suggestion & couldn't
> > > get it to work correctly.
> > >
> > > The idea is that the user selects from the validation list & the appropriate
> > > code is picked from the hidden list underneath.
> > >
> > > Suggestions?
> > >
> > >
> > > "Toppers" wrote:
> > >
> > > > HLOOKUP works on the full text.
> > > >
> > > > I tested a table with Corn & Carrots and it worked OK.
> > > >
> > > > Have you included FALSE as the last parameter to find an EXACT Match? If
> > > > not, it will find the closest match, assuming data is sorted in ascending
> > > > order.
> > > >
> > > > .
> > > > =HLOOKUP(D1,F1:J2,2,FALSE)
> > > >
> > > > HTH
> > > >
> > > > "The Toaster" wrote:
> > > >
> > > > > I am creating a workbook that is part of budget management. Within the
> > > > > workbook is a list of products (i.e: apples, pears, oranges) & their budget
> > > > > code. People need to be able to add new codes all the time. This data is
> > > > > called through to the main spreadsheet & listed in a hidden table. From here
> > > > > to data is shown in validation pick lists & the appropriate code is pulled
> > > > > using HLOOKUP from the hidden table.
> > > > >
> > > > > Unfortunately due to the stupidity of the users we have to keep as much of
> > > > > the workbook locked & restrict useage.
> > > > >
> > > > > The problem is that when we have two items with the same starting letter
> > > > > (i.e corn & carrotts) HLOOKUP is only picking up the first code it comes two
> > > > > & not comparing the full text.......help!
> > > > >