2010 Excel Vlookup works fine except on item search

FFNStaff
FFNStaff used Ask the Experts™
on
Hello,

=IF(VLOOKUP(A1117,'Historical Detail'!$A$2:$E$96,5,FALSE),VLOOKUP(J1117-0.02,Grid,2,FALSE),IF(ISNUMBER(MATCH(J1117,Payouta,0)),VLOOKUP(J1117,Grid,2,FALSE),VLOOKUP(J1117,OffGrid,2,FALSE)))

I am at wits end because this problem makes no sense!

The above formula works great except in 1 scenario: a rate of 82% on which the 2% is subtracted.  The 2nd vlookup does not seem to see the 80% on the Grid.  It sees it when it doesn't have to subtract and it sees it for every other number of the Grid--there are 10 lines on the grid and 91 other instances where the Grid works.   Any suggestions as to why this won't work?

I have made sure the Historical Detail range includes the 2 instances and copied the formatting from other cells in the Grid.

On the Function Arguments window for the 2nd vlookup, is there a way to expand the items shown in the window?

Thank you.

Pat
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2015

Commented:
Try to use this way...

=IF(VLOOKUP(A1117,'Historical Detail'!$A$2:$E$96,5,FALSE),VLOOKUP(round(J1117-0.02,0),Grid,2,FALSE),IF(ISNUMBER(MATCH(J1117,Payouta,0)),VLOOKUP(J1117,Grid,2,FALSE),VLOOKUP(J1117,OffGrid,2,FALSE)))

Open in new window


Saurabh...

Author

Commented:
Round didn't help, Saurabh, but thank you for responding.  

The 2nd vlookup does the math correctly to get to 80%, it just doesn't find it on the grid.  Could the fact that there is an 82% on the grid be causing the problem?

Pat
Top Expert 2015

Commented:
Is it possible to have a look over sample file?? Because without looking over it's hard to say whats really happening..
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Author

Commented:
Here is the document.

Author

Commented:
Here is the document.
Sample-Summary-Qtrly-Report.xlsx
Top Expert 2015

Commented:
Can you help me connect which value is it that you get an error?? Also tell me the guy name because your assumptions changes for each guy...

Author

Commented:
The names that don't work are:  Don Tho, Louise and Ever (pg 24).  The Level for these should come up at 1,300 rather than #N/A.
Top Expert 2015
Commented:
Use this one..it gives you desired results...

=IF(VLOOKUP(A17,Historical_Detail!$A$2:$E$65,5,FALSE),VLOOKUP(ROUND(J17-0.02,2),Grid,2,0),IF(ISNUMBER(MATCH(J17,Payouta,0)),VLOOKUP(J17,Grid,2,FALSE),VLOOKUP(J17,OffGrid,2,FALSE)))

Open in new window


Saurabh...

Author

Commented:
Saurabh, thank you!  I'm curious, why does using a 0 rather then False work?
Top Expert 2015

Commented:
It's not 0 ..even if you write false it will works..Its round formula which is making that change what it does it rounds it down to 2 decimals there by making the formula work...

Author

Commented:
Thank you so much!  I have been so frustrated by this problem.  I've spent hours trying to figure it out!!  Have a great day.

Pat

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial