VLOOKUP not returning correct answer

[Alan Davies]

I've a spreadseeht that calcualtes commission payments based on different
products
It uses validated lists for some data entry and then looksup the commission
value as follows:

Layout is as follows:
Column I - Product List
Column J - quantity of products
Column R currently is where I want the calcualtion to delvier the value.
Lists are PRODUCT and PRODS

If use the following formula:
=VLOOKUP(I3,prods,2,0)*J3
I get an #N/A error - which then means that the column won't total to let me
know how much i've made!

If I use:
=VLOOKUP(I3,prods,2,TRUE)*J3
all the products of a certain type return values based on one cell only. (5
products have similiar but differnt names, and different values in column 2,
yet the only number that is calculated comes from teh third product only)

If I use:
=VLOOKUP(I3,prods,2,FALSE)*J3
most cells then produce a #N/A answer, which ought to be a 0, and the cells
that should return a value actually return the correct value based on the
lookup table.

How can I get the answer I want which includes no #N/A and the right value
based on column 2 of the prods table?

TIA

 

[aidan.heritage]

=VLOOKUP(I3,prods,2,FALSE)*J3

sounds as though it should produce the right result, as FALSE
indicates you only want exact matches not nearest matches - so my GUESS
would be that you have rogue spaces at the end of EITHER the data
entries OR the lookup table (or worse, both!)

If it helps to email me a file to look at I'm at
(e-mail address removed)

 

[aidan.heritage]

I forgot to mention in my previous reply that IF you will have entries
that don't match, and you want these to produce zero, you will need to
put in an error trap

easiest one is

=if(iserror(vlookup(etc)),0,vlookup(etc))

 

[Ian P]

I would get around it by using the formula:

=if(iserror(VLOOKUP(I3,prods,2,0)),0,VLOOKUP(I3,prods,2,0)*J3)

HTH,

Ian