Avatar of FFNStaff
FFNStaff
Flag for United States of America asked on

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
Microsoft Excel

Avatar of undefined
Last Comment
FFNStaff

8/22/2022 - Mon
Saurabh Singh Teotia

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

ASKER
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
Saurabh Singh Teotia

Is it possible to have a look over sample file?? Because without looking over it's hard to say whats really happening..
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
FFNStaff

ASKER
Here is the document.
FFNStaff

ASKER
Here is the document.
Sample-Summary-Qtrly-Report.xlsx
Saurabh Singh Teotia

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...
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
FFNStaff

ASKER
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.
ASKER CERTIFIED SOLUTION
Saurabh Singh Teotia

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
FFNStaff

ASKER
Saurabh, thank you!  I'm curious, why does using a 0 rather then False work?
Saurabh Singh Teotia

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...
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
FFNStaff

ASKER
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