vlookup question

O

Omakbob

I have a very large spreadsheet with multiple sales weasels on it that
contains their gross commissions for the month. The columns are: 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
 
D

Don Guillett

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

Trevor Shuttleworth

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
 
O

Omakbob

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)
 
O

Omakbob

Thank you, Thank you, Thank you!

Trevor Shuttleworth said:
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
Click to expand...
 
D

Don Guillett

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.
 
Top