2010 Excel Vlookup works fine except on item search

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
FFNStaffAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
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...
0
FFNStaffAuthor 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
0
Saurabh Singh TeotiaCommented:
Is it possible to have a look over sample file?? Because without looking over it's hard to say whats really happening..
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

FFNStaffAuthor Commented:
Here is the document.
0
FFNStaffAuthor Commented:
Here is the document.
Sample-Summary-Qtrly-Report.xlsx
0
Saurabh Singh TeotiaCommented:
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...
0
FFNStaffAuthor 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.
0
Saurabh Singh TeotiaCommented:
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...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FFNStaffAuthor Commented:
Saurabh, thank you!  I'm curious, why does using a 0 rather then False work?
0
Saurabh Singh TeotiaCommented:
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...
0
FFNStaffAuthor 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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.