+ Reply to Thread
Results 1 to 21 of 21

VLookup and Rounding If No Match

  1. #1
    Registered User
    Join Date
    05-27-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    53

    VLookup and Rounding If No Match

    Hi,

    I have a grading table which is attached.

    I'm using a VLookup Formula at the moment and what I'm trying to do is the following:

    * If there is an exact match, award the appropriate grade E.G. If score is '10' then 'Poor'
    * If there is not an exact match, round up or down to nearest grade. E.G If score is '41' then 'Above Average'.
    * If there's a split difference though, always round up. E.G. If the user gets 5 round up to 10 rather than 0.
    * If the cell is blank or an invalid value is entered, have the cell default to the text "Not Graded". E.G. If text is entered, an invalid number like 101 is entered or nothing is entered this should be the defaultetc.

    Tried to have a go in G5 but didn't work out. Thank you in advance. BTB
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VLookup and Rounding If No Match

    Hi,

    One way

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: VLookup and Rounding If No Match

    Try this
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Punnam

  4. #4
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: VLookup and Rounding If No Match

    @ Richard Buttrey

    Roundup will deal with decimals ,
    but in your formula, it is rounding the number directly when you used "-1" could please explain in more detail about "-1"

    Punnam

  5. #5
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: VLookup and Rounding If No Match

    Try this ...

    =IF(OR(E5="",E5>100),"Not Graded",IFERROR(INDEX($B$5:$B$11,MATCH(E5,$A$5:$A$11,0)),
    IF(E5>=LOOKUP(E5,$A$5:$A$11,($A$5:$A$11+$A$6:$A$12)/2),
    LOOKUP(E5,$A$5:$A$11,$B$6:$B$12),LOOKUP(E5,$A$5:$A$11,$B$5:$B$11))))

  6. #6
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: VLookup and Rounding If No Match

    Try
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  7. #7
    Registered User
    Join Date
    05-27-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: VLookup and Rounding If No Match

    Hi All,

    Thank you so much for your help with this and for your excellent formulas. Just a few queries?

    * Is it possible to get a score of lower than 5 to round down to 'No Marks' as well. I know this seems strange but is this possible?
    * Richard, If I enter 40, the result is 'Average' but if I make it 41, it goes to 'Above Average'. Seems to be one point away from actual.
    * AVK, seems like if decimals are entered like 39.9 then it rounds it off to 40.
    * Punnam, like all the formulas, if you enter '92' it goes to 'Top' rather than rounding off to nearest 'Outstanding'.

    I'm sorry as I may not have explained it very well.

    Thank you again for all of your help with this. It's greatly appreciated and there's no way I would have been able to come up with anything like the formulas you presented. One more question, if the rating table was amended, would this affect the formulas?

    Thank you so much. BTB

  8. #8
    Registered User
    Join Date
    05-27-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: VLookup and Rounding If No Match

    Hi Phuocam, Thank you for the formula. How I got a "#Name?" error? I may be doing something wrong.

    Thank you, BTB.

  9. #9
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: VLookup and Rounding If No Match

    See this ...
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    05-27-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: VLookup and Rounding If No Match

    Hi Phuocam, I see from your sheet it works. However, as soon as I change a score for example, I still get that name error. Sorry about this. BTB

  11. #11
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: VLookup and Rounding If No Match

    Quote Originally Posted by BrownTeddyBear View Post
    Hi Phuocam, Thank you for the formula. How I got a "#Name?" error? I may be doing something wrong.

    Thank you, BTB.
    Formula for excel 2003:

    =IF(OR(E5="",E5>100),"Not Graded",IF(COUNTIF($A$5:$A$11,E5),
    VLOOKUP(E5,$A$5:$B$11,2,0),IF(E5>=LOOKUP(E5,$A$5:$A$11,($A$5:$A$11+$A$6:$A$12)/2),
    LOOKUP(E5,$A$5:$A$11,$B$6:$B$12),LOOKUP(E5,$A$5:$A$11,$B$5:$B$11))))
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    05-27-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: VLookup and Rounding If No Match

    Hi Phuocam, That's excellent and works very well. Thank you for sharing. Only thing I could find was if I entered -1, I got a #N/A error rather than 'Not Graded' but it's just what I needed so thank you. Thank you so much. BTB

  13. #13
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VLookup and Rounding If No Match

    Quote Originally Posted by Punnam View Post
    @ Richard Buttrey

    Roundup will deal with decimals ,
    but in your formula, it is rounding the number directly when you used "-1" could please explain in more detail about "-1"

    Punnam
    Slight modification

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Re the Roundup -1. From the function help


    ROUNDUP behaves like ROUND, except that it always rounds a number up.
    If num_digits is greater than 0 (zero), then number is rounded up to the specified number of decimal places.
    If num_digits is 0, then number is rounded up to the nearest integer.
    If num_digits is less than 0, then number is rounded up to the left of the decimal point.

  14. #14
    Registered User
    Join Date
    05-27-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: VLookup and Rounding If No Match

    Hi Richard, Thank you for the explanation and for your help. I will try this formula tonight.

    Hi Phuocam, In relation to your formula:
    Please Login or Register  to view this content.
    Is there a way to make the cell equal "Not Graded" if anything but a valid score is entered. For example, if something like '-1' is entered and is generating an '#NA', is it possible to make this generate a 'Not Graded'. Like if an error is made, 'Not Graded' is always the default?

    Thank you for your help. BTB

  15. #15
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: VLookup and Rounding If No Match

    Insert column A, create helper column

    In A5:

    =AVERAGE(B4:B5) and copy down.

    In G5:

    =IF(OR(F5="",F5>100,F5<0,ISTEXT(F5)),"Not Graded",VLOOKUP(F5,$A$5:$C$11,3,1))
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    05-27-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: VLookup and Rounding If No Match

    Hi Richard, Thank you for providing your formula and explaining how it works. I found it very helpful.

    Hi Phuocam, Thank you for your formula too which works perfectly and is exactly what I needed. Just one last question, with the column helper, does this have to be column A or can it be put in column d for example? If so, how does this affect the syntax of the formula? Just trying to get my head around the VLookup function.

    Thank you for all of your help again. BTB

  17. #17
    Registered User
    Join Date
    05-27-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: VLookup and Rounding If No Match

    Hi Punnam and Richard, In relation to your formulas, is it possible to round off to the nearest benchmark instead of the next highest benchmark. For example, 4.9 would equal 'No Marks' rather than 'Poor' and 92 would equal 'Outstanding' rather than 'Top'? Thank you, BTB.
    Last edited by BrownTeddyBear; 06-07-2017 at 11:25 PM.

  18. #18
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VLookup and Rounding If No Match

    Hi,

    Try

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  19. #19
    Registered User
    Join Date
    05-27-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: VLookup and Rounding If No Match

    Hi Richard,

    Thank you so much for the formula. 'No Marks' works perfectly. It goes up until 4.99 as 'No Marks' and as soon as it hits 5, goes to 'Poor'. However, if I put a score of 20.1 in for example, the result stays at 'Poor' instead of going to 'Average' which is the next closest integer. Same for all of the benchmarks going up. I think this may have something to do with the rating table increasing by 20 for some benchmarks as opposed to 10.

    This is the formula which also includes now an error check for data entered less than 0.

    Please Login or Register  to view this content.
    Any help would be appreciated as always. Sorry to be a nuisance.

    Thank you, BTB

  20. #20
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VLookup and Rounding If No Match

    Hi,

    Maybe the simplest way is to change the lookup table to

    0 No Marks
    5 Poor
    20 Average
    40 Above Average
    60 Excellent
    80 Outstanding
    95 Top

    then use a formula with a normal ROUND function, i.e.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  21. #21
    Registered User
    Join Date
    05-27-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: VLookup and Rounding If No Match

    Hi Richard, Thank you for your response, and as advised I have amended the grading table. I think Phuocam also suggested this earlier with the column helper which was an excellent solution too. The only thing I would say Richard is that I found if I put 4.9999 in a cell for example, it would round up to 'Poor' rather than 'No Marks'. A minor thing which probably wouldn't be an issue though. Thank you for the great solution.

    I'd just like to say thank you to everybody for their help with my enquiry. I'm very grateful for all the assistance and for the solutions which were provided. Thank you, BTB.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Calculation error when using VLOOKUP due to rounding
    By Baconfish in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-25-2017, 02:46 PM
  2. Rounding numbers or match lookup?
    By smudgepost in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-18-2014, 09:25 AM
  3. Replies: 6
    Last Post: 05-01-2013, 10:10 AM
  4. Vlookup PREDETERMINED range and ROUNDING Help Plz
    By siegreen in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-01-2010, 07:29 AM
  5. [SOLVED] rounding numbers for match function
    By erc_blair in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-14-2006, 11:15 AM
  6. rounding numbers for match function
    By erc_blair in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-13-2006, 09:25 PM
  7. Vlookup Rounding
    By wmaughan in forum Excel General
    Replies: 9
    Last Post: 12-27-2005, 08:00 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1