Messy Formuula Needs Clean up Help: Part 2 (Clarification Please)!

Check out Excel's Help for the VLOOKUP() function, it has more detail.

In the specific formula, =VLOOKUP(A5,AA1:AB7,2,0)
A5 is the cell holding the information you want to match in the first column
of the lookup table (cells AA1:AA7).
AA1:AB7 is the lookup table itself, and the item to be searched for (that A5
value) will always be searched for in the 1st column of the table.
The ,2, part says to return the value from the 2nd column of the lookup
table when a match is found in the first column. Note that if no match is
found, the VLOOKUP() will return #N/A.
Finally, the ",0)" [which can also be written as ,FALSE)] says that the
items in the first column may or may not be ordered. So it works even if
that list is just all jumbled up.

As for "what does A5 reference?". Based on the rest of your original write
up, I would imagine that Biff meant for it to reference the cell where the
drop down is at (if you're using data validation) or to the Linked cell if
you are using a combo box control.

As for where can you put the formula? Just about anywhere on the sheet. As
written, the formula is set up with the thought that A5 and AA1:AB7 are all
on the same sheet.

Hope this helps some.

"Danny Boy" wrote:

I posted the question below earlier for Biff, however, given that I
previously said that the question was resolved (clicking thank you on the
feedback request option), when it turned out I was still having difficulty, I
was not certain whether or not my additional clarification request got seen.
For that reason I am reposting as a new question. Please see “Messy Formula
Needs Clean up Help” comments for the original postings and responses that I
received (which now include the clarification I was asking Biff for).

Thanks everyone!
Dan

Hi Biff!

I tried your suggestion, and I'm still a bit confused (sorry)! My first
question is about your formula.

=VLOOKUP(A5,AA1:AB7,2,0)

1-What does cell A5 reference? Given that the strings and corresponding
numbers are in AA1-AA7 and AB1-AB7.

2-In what cell do I put the VLOOKUP formula so that it runs?

What I'm trying to (as you may have guessed), is to use a drop down menu
with the various charge types (Administrative Discharge Fee, Medication Non
Compliance Fee, Taxi Fees, etc) in one column, and then have the charges
themselves appear in the corresponding columns. If the drop down menu is
blank, than no charges would appear.

Again, Thank You

I think it is starting to make sense now. The formula goes in cell B5, and
the drop down menus which run the VLOOKUP are in cell A5, and the VLOOKUP
tables themselves are in cells AA2:AB9. One last question...

If I want the cell value outcome (in B5) to remain blank when no dropdown
items are selected (as opposed to #N/A), would the following formula be
appropriate:

=IF(A5="","",VLOOKUP(A5,AA2:AB9,2,0))

Again, thanks much for the clarification and education!

Dan

"JLatham" wrote:

Check out Excel's Help for the VLOOKUP() function, it has more detail.

In the specific formula, =VLOOKUP(A5,AA1:AB7,2,0)
A5 is the cell holding the information you want to match in the first column
of the lookup table (cells AA1:AA7).
AA1:AB7 is the lookup table itself, and the item to be searched for (that A5
value) will always be searched for in the 1st column of the table.
The ,2, part says to return the value from the 2nd column of the lookup
table when a match is found in the first column. Note that if no match is
found, the VLOOKUP() will return #N/A.
Finally, the ",0)" [which can also be written as ,FALSE)] says that the
items in the first column may or may not be ordered. So it works even if
that list is just all jumbled up.

As for "what does A5 reference?". Based on the rest of your original write
up, I would imagine that Biff meant for it to reference the cell where the
drop down is at (if you're using data validation) or to the Linked cell if
you are using a combo box control.

As for where can you put the formula? Just about anywhere on the sheet. As
written, the formula is set up with the thought that A5 and AA1:AB7 are all
on the same sheet.

Hope this helps some.

"Danny Boy" wrote:

> I posted the question below earlier for Biff, however, given that I
> previously said that the question was resolved (clicking thank you on the
> feedback request option), when it turned out I was still having difficulty, I
> was not certain whether or not my additional clarification request got seen.
> For that reason I am reposting as a new question. Please see “Messy Formula
> Needs Clean up Help” comments for the original postings and responses that I
> received (which now include the clarification I was asking Biff for).
>
> Thanks everyone!
> Dan
>
> Hi Biff!
>
> I tried your suggestion, and I'm still a bit confused (sorry)! My first
> question is about your formula.
>
> =VLOOKUP(A5,AA1:AB7,2,0)
>
> 1-What does cell A5 reference? Given that the strings and corresponding
> numbers are in AA1-AA7 and AB1-AB7.
>
> 2-In what cell do I put the VLOOKUP formula so that it runs?
>
> What I'm trying to (as you may have guessed), is to use a drop down menu
> with the various charge types (Administrative Discharge Fee, Medication Non
> Compliance Fee, Taxi Fees, etc) in one column, and then have the charges
> themselves appear in the corresponding columns. If the drop down menu is
> blank, than no charges would appear.
>
> Again, Thank You
>

would the following formula be appropriate:
=IF(A5="","",VLOOKUP(A5,AA2:AB9,2,0))

Yes. However, if A5 contained some entry that is not listed in the first
column of the lookup table, AA2:AA9, then you'll still get the #N/A error.

If you want to trap *all* possible errors then you need to do something like
this:

=IF(ISERROR(VLOOKUP(A5,AA2:AB9,2,0)),"",VLOOKUP(A5,AA2:AB9,2,0))

If you're using Excel 2007 that can be reduced to:

=IFERROR(VLOOKUP(A5,AA2:AB9,2,0),"")

Biff
Microsoft Excel MVP


"Danny Boy" wrote in message
news:

I think it is starting to make sense now. The formula goes in cell B5, and
the drop down menus which run the VLOOKUP are in cell A5, and the VLOOKUP
tables themselves are in cells AA2:AB9. One last question...

If I want the cell value outcome (in B5) to remain blank when no dropdown
items are selected (as opposed to #N/A), would the following formula be
appropriate:

=IF(A5="","",VLOOKUP(A5,AA2:AB9,2,0))

Again, thanks much for the clarification and education!

Dan

"JLatham" wrote:

Check out Excel's Help for the VLOOKUP() function, it has more detail.

In the specific formula, =VLOOKUP(A5,AA1:AB7,2,0)
A5 is the cell holding the information you want to match in the first
column
of the lookup table (cells AA1:AA7).
AA1:AB7 is the lookup table itself, and the item to be searched for (that
A5
value) will always be searched for in the 1st column of the table.
The ,2, part says to return the value from the 2nd column of the lookup
table when a match is found in the first column. Note that if no match
is
found, the VLOOKUP() will return #N/A.
Finally, the ",0)" [which can also be written as ,FALSE)] says that the
items in the first column may or may not be ordered. So it works even if
that list is just all jumbled up.

As for "what does A5 reference?". Based on the rest of your original
write
up, I would imagine that Biff meant for it to reference the cell where
the
drop down is at (if you're using data validation) or to the Linked cell
if
you are using a combo box control.

As for where can you put the formula? Just about anywhere on the sheet.
As
written, the formula is set up with the thought that A5 and AA1:AB7 are
all
on the same sheet.

Hope this helps some.

"Danny Boy" wrote:

> I posted the question below earlier for Biff, however, given that I
> previously said that the question was resolved (clicking thank you on
> the
> feedback request option), when it turned out I was still having
> difficulty, I
> was not certain whether or not my additional clarification request got
> seen.
> For that reason I am reposting as a new question. Please see "Messy
> Formula
> Needs Clean up Help" comments for the original postings and responses
> that I
> received (which now include the clarification I was asking Biff for).
>
> Thanks everyone!
> Dan
>
> Hi Biff!
>
> I tried your suggestion, and I'm still a bit confused (sorry)! My first
> question is about your formula.
>
> =VLOOKUP(A5,AA1:AB7,2,0)
>
> 1-What does cell A5 reference? Given that the strings and corresponding
> numbers are in AA1-AA7 and AB1-AB7.
>
> 2-In what cell do I put the VLOOKUP formula so that it runs?
>
> What I'm trying to (as you may have guessed), is to use a drop down
> menu
> with the various charge types (Administrative Discharge Fee, Medication
> Non
> Compliance Fee, Taxi Fees, etc) in one column, and then have the
> charges
> themselves appear in the corresponding columns. If the drop down menu
> is
> blank, than no charges would appear.
>
> Again, Thank You
>

Your solution to keep it blank should work, but here's an alternative with a
more generic use:

=IF(ISNA(VLOOKUP(A5,AA2:AB9,2,0)),"",VLOOKUP(A5,AA2:AB9,2,0))
What this does is return a zero length string anytime the VLOOKUP() comes up
with no match for any reason. You'll see this type of setup a lot of times
when VLOOKUP(), or HLOOKUP(), is used with a chance of a #N/A popping up.

Also, a couple of hints for future use. While your situation only requires
the one formula, a lot of times there is need to fill a VLOOKUP() formula
down into a list of things (say part numbers) to pull related information
from somewhere else. As you fill the list down, suddenly you find things not
working right and it usually because the address of the table changes. To
prevent that from happening, use the absolute address 'marker' to keep the
table address stable, as:
VLOOKUP(A5,$AA$2:$AB$9,2,0)


"Danny Boy" wrote:

I think it is starting to make sense now. The formula goes in cell B5, and
the drop down menus which run the VLOOKUP are in cell A5, and the VLOOKUP
tables themselves are in cells AA2:AB9. One last question...

If I want the cell value outcome (in B5) to remain blank when no dropdown
items are selected (as opposed to #N/A), would the following formula be
appropriate:

=IF(A5="","",VLOOKUP(A5,AA2:AB9,2,0))

Again, thanks much for the clarification and education!

Dan

"JLatham" wrote:

> Check out Excel's Help for the VLOOKUP() function, it has more detail.
>
> In the specific formula, =VLOOKUP(A5,AA1:AB7,2,0)
> A5 is the cell holding the information you want to match in the first column
> of the lookup table (cells AA1:AA7).
> AA1:AB7 is the lookup table itself, and the item to be searched for (that A5
> value) will always be searched for in the 1st column of the table.
> The ,2, part says to return the value from the 2nd column of the lookup
> table when a match is found in the first column. Note that if no match is
> found, the VLOOKUP() will return #N/A.
> Finally, the ",0)" [which can also be written as ,FALSE)] says that the
> items in the first column may or may not be ordered. So it works even if
> that list is just all jumbled up.
>
> As for "what does A5 reference?". Based on the rest of your original write
> up, I would imagine that Biff meant for it to reference the cell where the
> drop down is at (if you're using data validation) or to the Linked cell if
> you are using a combo box control.
>
> As for where can you put the formula? Just about anywhere on the sheet. As
> written, the formula is set up with the thought that A5 and AA1:AB7 are all
> on the same sheet.
>
> Hope this helps some.
>
> "Danny Boy" wrote:
>
> > I posted the question below earlier for Biff, however, given that I
> > previously said that the question was resolved (clicking thank you on the
> > feedback request option), when it turned out I was still having difficulty, I
> > was not certain whether or not my additional clarification request got seen.
> > For that reason I am reposting as a new question. Please see “Messy Formula
> > Needs Clean up Help” comments for the original postings and responses that I
> > received (which now include the clarification I was asking Biff for).
> >
> > Thanks everyone!
> > Dan
> >
> > Hi Biff!
> >
> > I tried your suggestion, and I'm still a bit confused (sorry)! My first
> > question is about your formula.
> >
> > =VLOOKUP(A5,AA1:AB7,2,0)
> >
> > 1-What does cell A5 reference? Given that the strings and corresponding
> > numbers are in AA1-AA7 and AB1-AB7.
> >
> > 2-In what cell do I put the VLOOKUP formula so that it runs?
> >
> > What I'm trying to (as you may have guessed), is to use a drop down menu
> > with the various charge types (Administrative Discharge Fee, Medication Non
> > Compliance Fee, Taxi Fees, etc) in one column, and then have the charges
> > themselves appear in the corresponding columns. If the drop down menu is
> > blank, than no charges would appear.
> >
> > Again, Thank You
> >

Thanks for all the help everyone! I love the learning that this discussion
group provides me with. To be honest, all of my Excel knowledge has come from
you all!

Best,

Dan

"JLatham" wrote:

Your solution to keep it blank should work, but here's an alternative with a
more generic use:

=IF(ISNA(VLOOKUP(A5,AA2:AB9,2,0)),"",VLOOKUP(A5,AA2:AB9,2,0))
What this does is return a zero length string anytime the VLOOKUP() comes up
with no match for any reason. You'll see this type of setup a lot of times
when VLOOKUP(), or HLOOKUP(), is used with a chance of a #N/A popping up.

Also, a couple of hints for future use. While your situation only requires
the one formula, a lot of times there is need to fill a VLOOKUP() formula
down into a list of things (say part numbers) to pull related information
from somewhere else. As you fill the list down, suddenly you find things not
working right and it usually because the address of the table changes. To
prevent that from happening, use the absolute address 'marker' to keep the
table address stable, as:
VLOOKUP(A5,$AA$2:$AB$9,2,0)


"Danny Boy" wrote:

> I think it is starting to make sense now. The formula goes in cell B5, and
> the drop down menus which run the VLOOKUP are in cell A5, and the VLOOKUP
> tables themselves are in cells AA2:AB9. One last question...
>
> If I want the cell value outcome (in B5) to remain blank when no dropdown
> items are selected (as opposed to #N/A), would the following formula be
> appropriate:
>
> =IF(A5="","",VLOOKUP(A5,AA2:AB9,2,0))
>
> Again, thanks much for the clarification and education!
>
> Dan
>
> "JLatham" wrote:
>
> > Check out Excel's Help for the VLOOKUP() function, it has more detail.
> >
> > In the specific formula, =VLOOKUP(A5,AA1:AB7,2,0)
> > A5 is the cell holding the information you want to match in the first column
> > of the lookup table (cells AA1:AA7).
> > AA1:AB7 is the lookup table itself, and the item to be searched for (that A5
> > value) will always be searched for in the 1st column of the table.
> > The ,2, part says to return the value from the 2nd column of the lookup
> > table when a match is found in the first column. Note that if no match is
> > found, the VLOOKUP() will return #N/A.
> > Finally, the ",0)" [which can also be written as ,FALSE)] says that the
> > items in the first column may or may not be ordered. So it works even if
> > that list is just all jumbled up.
> >
> > As for "what does A5 reference?". Based on the rest of your original write
> > up, I would imagine that Biff meant for it to reference the cell where the
> > drop down is at (if you're using data validation) or to the Linked cell if
> > you are using a combo box control.
> >
> > As for where can you put the formula? Just about anywhere on the sheet. As
> > written, the formula is set up with the thought that A5 and AA1:AB7 are all
> > on the same sheet.
> >
> > Hope this helps some.
> >
> > "Danny Boy" wrote:
> >
> > > I posted the question below earlier for Biff, however, given that I
> > > previously said that the question was resolved (clicking thank you on the
> > > feedback request option), when it turned out I was still having difficulty, I
> > > was not certain whether or not my additional clarification request got seen.
> > > For that reason I am reposting as a new question. Please see “Messy Formula
> > > Needs Clean up Help” comments for the original postings and responses that I
> > > received (which now include the clarification I was asking Biff for).
> > >
> > > Thanks everyone!
> > > Dan
> > >
> > > Hi Biff!
> > >
> > > I tried your suggestion, and I'm still a bit confused (sorry)! My first
> > > question is about your formula.
> > >
> > > =VLOOKUP(A5,AA1:AB7,2,0)
> > >
> > > 1-What does cell A5 reference? Given that the strings and corresponding
> > > numbers are in AA1-AA7 and AB1-AB7.
> > >
> > > 2-In what cell do I put the VLOOKUP formula so that it runs?
> > >
> > > What I'm trying to (as you may have guessed), is to use a drop down menu
> > > with the various charge types (Administrative Discharge Fee, Medication Non
> > > Compliance Fee, Taxi Fees, etc) in one column, and then have the charges
> > > themselves appear in the corresponding columns. If the drop down menu is
> > > blank, than no charges would appear.
> > >
> > > Again, Thank You
> > >