vlookup question
 

I have a very large spreadsheet with multiple sales weasels on it that
contains their gross commissions for the month. The columns a Client;
Insurance Carrier; Salesperson Class; Salesperson; Jan, Feb, March (etc for
each month that commissions are received for that client.

On another spreadsheet within the same workbook I want to just pull those
commissions received from specific clients, so that:

Bob's Auto Garage is in column A, the vlookup will also give me the amount
from column F.

The formula that I have that isn't working is:
=VLOOKUP('Com 06'!A:A="Bob's Auto Garage",'Com 06'!F:F)

The result is a #VALUE! error.

Would someone please tell me where I'm going wrong?

Thanks

vlookup question
 

As a life long salesman (an excel playboy), I am curious as to how you
define "weasels"

--
Don Guillett
SalesAid Software

"Omakbob" wrote in message
...
I have a very large spreadsheet with multiple sales weasels on it that
contains their gross commissions for the month. The columns a Client;
Insurance Carrier; Salesperson Class; Salesperson; Jan, Feb, March (etc
for
each month that commissions are received for that client.

On another spreadsheet within the same workbook I want to just pull those
commissions received from specific clients, so that:

Bob's Auto Garage is in column A, the vlookup will also give me the amount
from column F.

The formula that I have that isn't working is:
=VLOOKUP('Com 06'!A:A="Bob's Auto Garage",'Com 06'!F:F)

The result is a #VALUE! error.

Would someone please tell me where I'm going wrong?

Thanks

vlookup question
 

Try it this way:

=IF(ISNA(VLOOKUP("Bob's Auto Garage",'COM 06'!$A:$F,6,FALSE)),"Not
Found",VLOOKUP("Bob's Auto Garage",'COM 06'!$A:$F,6,FALSE))

The first parameter is what you're looking for, Bob's Auto Garage

The second parameter is where to look for it ... an array of data. VLOOKUP
always looks down the first column, in this case, column A on sheet COM 06.

The third parameter is the column to go to for the return value, relative to
the first column; in this case, column 6 = column F ... that's if it finds
anything.

The fourth and last parameter is set to FALSE so that VLOOKUP will return an
exact match or nothing. If Bob's Auto Garage is not found, you'll get back
an #N/A! error.

Lastly, wrapping the VLOOKUP in the IF(ISNA(...) construct allows you to
control what you get back if it doesn't find what you're looking for.

You wouldn't normally use a fixed Lookup value so you might expect the
formula to look something like:

=IF(ISNA(VLOOKUP(A2,'COM 06'!$A:$F,6,FALSE)), "Not Found", VLOOKUP(A2,'COM
06'!$A:$F,6,FALSE))

Where A2 contains Bob's Auto Garage

Regards

Trevor


"Omakbob" wrote in message
...
I have a very large spreadsheet with multiple sales weasels on it that
contains their gross commissions for the month. The columns a Client;
Insurance Carrier; Salesperson Class; Salesperson; Jan, Feb, March (etc
for
each month that commissions are received for that client.

On another spreadsheet within the same workbook I want to just pull those
commissions received from specific clients, so that:

Bob's Auto Garage is in column A, the vlookup will also give me the amount
from column F.

The formula that I have that isn't working is:
=VLOOKUP('Com 06'!A:A="Bob's Auto Garage",'Com 06'!F:F)

The result is a #VALUE! error.

Would someone please tell me where I'm going wrong?

Thanks

vlookup question
 

D'oh! busted :)
My definition of a 'sales weasel' is a salesperson who promises more than
the product is/was meant to deliver in order to make the sale. (Be gentle,
I'm an accounting troll)

"Don Guillett" wrote:

As a life long salesman (an excel playboy), I am curious as to how you
define "weasels"

--
Don Guillett
SalesAid Software

"Omakbob" wrote in message
...
I have a very large spreadsheet with multiple sales weasels on it that
contains their gross commissions for the month. The columns a Client;
Insurance Carrier; Salesperson Class; Salesperson; Jan, Feb, March (etc
for
each month that commissions are received for that client.

On another spreadsheet within the same workbook I want to just pull those
commissions received from specific clients, so that:

Bob's Auto Garage is in column A, the vlookup will also give me the amount
from column F.

The formula that I have that isn't working is:
=VLOOKUP('Com 06'!A:A="Bob's Auto Garage",'Com 06'!F:F)

The result is a #VALUE! error.

Would someone please tell me where I'm going wrong?

Thanks

vlookup question
 

Oh, a pointy headed pencil pusher, huh.

--
Don Guillett
SalesAid Software

"Omakbob" wrote in message
...
D'oh! busted :)
My definition of a 'sales weasel' is a salesperson who promises more than
the product is/was meant to deliver in order to make the sale. (Be
gentle,
I'm an accounting troll)

"Don Guillett" wrote:

As a life long salesman (an excel playboy), I am curious as to how you
define "weasels"

--
Don Guillett
SalesAid Software

"Omakbob" wrote in message
...
I have a very large spreadsheet with multiple sales weasels on it that
contains their gross commissions for the month. The columns a
Client;
Insurance Carrier; Salesperson Class; Salesperson; Jan, Feb, March (etc
for
each month that commissions are received for that client.

On another spreadsheet within the same workbook I want to just pull
those
commissions received from specific clients, so that:

Bob's Auto Garage is in column A, the vlookup will also give me the
amount
from column F.

The formula that I have that isn't working is:
=VLOOKUP('Com 06'!A:A="Bob's Auto Garage",'Com 06'!F:F)

The result is a #VALUE! error.

Would someone please tell me where I'm going wrong?

Thanks

vlookup question
 

Thank you, Thank you, Thank you!

"Trevor Shuttleworth" wrote:

Try it this way:

=IF(ISNA(VLOOKUP("Bob's Auto Garage",'COM 06'!$A:$F,6,FALSE)),"Not
Found",VLOOKUP("Bob's Auto Garage",'COM 06'!$A:$F,6,FALSE))

The first parameter is what you're looking for, Bob's Auto Garage

The second parameter is where to look for it ... an array of data. VLOOKUP
always looks down the first column, in this case, column A on sheet COM 06.

The third parameter is the column to go to for the return value, relative to
the first column; in this case, column 6 = column F ... that's if it finds
anything.

The fourth and last parameter is set to FALSE so that VLOOKUP will return an
exact match or nothing. If Bob's Auto Garage is not found, you'll get back
an #N/A! error.

Lastly, wrapping the VLOOKUP in the IF(ISNA(...) construct allows you to
control what you get back if it doesn't find what you're looking for.

You wouldn't normally use a fixed Lookup value so you might expect the
formula to look something like:

=IF(ISNA(VLOOKUP(A2,'COM 06'!$A:$F,6,FALSE)), "Not Found", VLOOKUP(A2,'COM
06'!$A:$F,6,FALSE))

Where A2 contains Bob's Auto Garage

Regards

Trevor


"Omakbob" wrote in message
...
I have a very large spreadsheet with multiple sales weasels on it that
contains their gross commissions for the month. The columns a Client;
Insurance Carrier; Salesperson Class; Salesperson; Jan, Feb, March (etc
for
each month that commissions are received for that client.

On another spreadsheet within the same workbook I want to just pull those
commissions received from specific clients, so that:

Bob's Auto Garage is in column A, the vlookup will also give me the amount
from column F.

The formula that I have that isn't working is:
=VLOOKUP('Com 06'!A:A="Bob's Auto Garage",'Com 06'!F:F)

The result is a #VALUE! error.

Would someone please tell me where I'm going wrong?

Thanks

vlookup question
 

I think you misspelled bean counter. <vbg

Don Guillett wrote:

Oh, a pointy headed pencil pusher, huh.

--
Don Guillett
SalesAid Software

"Omakbob" wrote in message
...
D'oh! busted :)
My definition of a 'sales weasel' is a salesperson who promises more than
the product is/was meant to deliver in order to make the sale. (Be
gentle,
I'm an accounting troll)

"Don Guillett" wrote:

As a life long salesman (an excel playboy), I am curious as to how you
define "weasels"

--
Don Guillett
SalesAid Software

"Omakbob" wrote in message
...
I have a very large spreadsheet with multiple sales weasels on it that
contains their gross commissions for the month. The columns a
Client;
Insurance Carrier; Salesperson Class; Salesperson; Jan, Feb, March (etc
for
each month that commissions are received for that client.

On another spreadsheet within the same workbook I want to just pull
those
commissions received from specific clients, so that:

Bob's Auto Garage is in column A, the vlookup will also give me the
amount
from column F.

The formula that I have that isn't working is:
=VLOOKUP('Com 06'!A:A="Bob's Auto Garage",'Com 06'!F:F)

The result is a #VALUE! error.

Would someone please tell me where I'm going wrong?

Thanks




--

Dave Peterson

vlookup question
 

So solly.

Actually, I would like to help but until he realizes that, without the
weasel he wouldn't have a bean to count. Maybe the thought was that there
are only accounting types here.

In my old insurance/stock brokerage firm we used to call the home office
management organization the "homos" <G

Heck, we gotta have some fun sometimes like Fridays on the L & G groups.

--
Don Guillett
SalesAid Software

"Dave Peterson" wrote in message
...
I think you misspelled bean counter. <vbg

Don Guillett wrote:

Oh, a pointy headed pencil pusher, huh.

--
Don Guillett
SalesAid Software

"Omakbob" wrote in message
...
D'oh! busted :)
My definition of a 'sales weasel' is a salesperson who promises more
than
the product is/was meant to deliver in order to make the sale. (Be
gentle,
I'm an accounting troll)

"Don Guillett" wrote:

As a life long salesman (an excel playboy), I am curious as to how you
define "weasels"

--
Don Guillett
SalesAid Software

"Omakbob" wrote in message
...
I have a very large spreadsheet with multiple sales weasels on it
that
contains their gross commissions for the month. The columns a
Client;
Insurance Carrier; Salesperson Class; Salesperson; Jan, Feb, March
(etc
for
each month that commissions are received for that client.

On another spreadsheet within the same workbook I want to just pull
those
commissions received from specific clients, so that:

Bob's Auto Garage is in column A, the vlookup will also give me the
amount
from column F.

The formula that I have that isn't working is:
=VLOOKUP('Com 06'!A:A="Bob's Auto Garage",'Com 06'!F:F)

The result is a #VALUE! error.

Would someone please tell me where I'm going wrong?

Thanks




--

Dave Peterson