Vlookup Returns A #n/a

  • Thread starter Thread starter nander
  • Start date Start date
N

nander

VLOOKUP returns #N/A
=IF(VLOOKUP(Sales!$D$3,'Incentiv
(2)'!$A$5:$G$10,6,1)>0,VLOOKUP(Sales!$D$3,'Incentiv
(2)'!$A$5:$G$10,6,1),0)

This formulae returns a #N/A When sales are 105% and below I want a
instead. What needs to be changed in the above formula?

This is the incentive(2) worksheet this formula references

Mature Store Ast Sales Office Warehouse
Store Incentive Manager Manager Associate

105% LY Sales $0.00 $0.00 $0.00
106% LY Sales $300.00 $150.00 $100.00 $50 $50
108% LY Sales $400.00 $200.00 $150.00 $50 $50
110% LY Sales $600.00 $300.00 $200.00 $50 $50
115% LY Sales $800.00 $400.00 $250.00 $50 $50
120% LY Sales $1,000.00 $500.00 $300.00 $50 $5
 
Hi
try
=IF(ISNA(VLOOKUP(Sales!$D$3,'Incentive(2)'!$A$5:$G$10,6,1)),0,VLOOKUP(S
ales!$D$3,'Incentive(2)'!$A$5:$G$10,6,1))
 
Frank when I make those changes I get a message that

the macros in this project are disabled




#NAME?
=IF(INSA(VLOOKUP(Sales!$D$3,'Incentiv
(2)'!$A$5:$G$10,6,1)),0,VLOOKUP(Sales!$D$3,'Incentiv
(2)'!$A$5:$G$10,6,1)
 
[...]
#NAME?
=IF(INSA(VLOOKUP(Sales!$D$3,'Incentive
(2)'!$A$5:$G$10,6,1)),0,VLOOKUP(Sales!$D$3,'Incentive
(2)'!$A$5:$G$10,6,1))

It's ISNA, not INSA as you have.

BTW, since the match-type is set to 1 in Vlookup, I'd think

=IF(Sales!$D3<>"",VLOOKUP(Sales!$D3,'Incentive(2)'!$A$5:$G$10,6,1),0)

would suffice.
 
DOTHAN
MONTH 2004 actual
JANUARY 6.00% $100,857.26 96.04%
FEBRUARY 6.00% 87,823.99 102.15%
MARCH 6.00% 132,890.41 122.38%
APRIL 6.00% 111,328.46 91.91%

Mature Store Ast Sales Office Warehouse
Store Incentive Manager Manager Associate

105% LY Sales $0.00 $0.00 $0.00
106% LY Sales $300.00 $150.00 $100.00 $50 $50
108% LY Sales $400.00 $200.00 $150.00 $50 $50
110% LY Sales $600.00 $300.00 $200.00 $50 $50
115% LY Sales $800.00 $400.00 $250.00 $50 $50
120% LY Sales $1,000.00 $500.00 $300.00 $50 $50

=IF(VLOOKUP(Sales!$D$3,'Incentiv
(2)'!$A$5:$E$10,3,1)>0,VLOOKUP(Sales!$D$3,'Incentiv
(2)'!$A$5:$E$10,6,1),0)

The first block above is from the Sales worksheet. The next is th
incentive2 worksheet. The vlookup formula refers to the Office worker
When the sales are less than 105% I get a #N/A. I've tried the formul
given and it does not work. I could be keying it wrong. Could I cut an
past it to the cell? I've included these worksheets incase I've lef
something out
 
Back
Top