+ Reply to Thread
Results 1 to 6 of 6

Using MAX & MIN function on vlookup results

  1. 07-20-2019, 10:25 AM #1
    rgwood
    rgwood is offline
    Registered User
    Join Date
    07-13-2018
    Location
    Manchester, England
    MS-Off Ver
    2010
    Posts
    7

    Using MAX & MIN function on vlookup results

    Hi all,

    I am sure I am probably missing a ridiculously simple solution, but for the life of me I cant find it myself.

    I have 10 cells which all contain a numeric value which is calculated based on vlookup formulas. I want to find the range between the lowest and highest numbers of these values, but when I use the simple =MAX(a1:a10), but the result I get is 0.

    It appears the fact that these numbers are displayed as a result of the formula, means that the MAX and MIN functions are not dealing with numbers to produce a result but perhaps trying to apply this to formulas which doesnt make sense, hence the 0 value result.

    Please can anyone shed any light on this for me. The values in A1:A10 will change in the future so have to take this into account.

    Thanks in advance

    Rob
    Reply With QuoteRegister To Reply
  2. 07-20-2019, 10:31 AM #2
    BanginMyHeadOnMyDesk
    BanginMyHeadOnMyDesk is offline
    Valued Forum Contributor
    Join Date
    06-02-2012
    Location
    London, England
    MS-Off Ver
    365
    Posts
    397

    Re: Using MAX & MIN function on vlookup results

    MAX should be able to work on values that are the result of formulas. I've just tested it and it works fine.

    Perhaps your values are actually text rather than numbers?

    Attach a sample workbook and we can take a look at that.

    B.
    Reply With QuoteRegister To Reply
  3. 07-20-2019, 10:38 AM #3
    Aardigspook
    Aardigspook is offline
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Using MAX & MIN function on vlookup results

    Your 'numeric values' are probably text which appear to be values.
    Two ways to fix this:

    1. Fix the source (where the values are coming from), by changing the format there to 'General' or 'Number.'

    2. Make the VLookup results into proper numbers by one of three methods:
    a. multiply the lookup by 1, like this: =VLOOKUP(...your lookup...)*1
    b. add 0 to the lookup: =VLOOKUP(...your lookup...)+0
    c. convert to a value using the double-unary (double-negative): =--VLOOKUP(...your lookup...)

    Hope that helps.
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.
    Reply With QuoteRegister To Reply
  4. 07-21-2019, 08:20 AM #4
    res46555
    res46555 is offline
    Registered User
    Join Date
    07-21-2019
    Location
    Riyadh, KSA
    MS-Off Ver
    10
    Posts
    1

    Re: Using MAX & MIN function on vlookup results

    if the vlookup results are all supposed to be values then try this..

    =VALUE(VLOOKUP(......................))

    it should make the results as value not text.
    Reply With QuoteRegister To Reply
  5. 07-22-2019, 12:40 AM #5
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Using MAX & MIN function on vlookup results

    When an excel formula references a cell(s), it looks at what is displayed in that cell, not the underlying contents. Meaning that it doesnt matter if a cell contains a formula (that produces 5) or an entry of 5, all excel sees is 5.

    You say that the cells contain the results of vlookup? What is being returned and what is teh data that it is coming from?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  6. 07-22-2019, 12:42 AM #6
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Using MAX & MIN function on vlookup results

    Quote Originally Posted by res46555 View Post
    if the vlookup results are all supposed to be values then try this..

    =VALUE(VLOOKUP(......................))

    it should make the results as value not text.
    not necessarily, it really does depend on what is being returned.
    1.1 will convert to numeric
    1.1.1 will always be text, no matter how you try to convert it.

    You could also achieve the same results like this...
    =--VLOOKUP(......................)
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. discrepancy in vlookup function results
    By nazaninibb in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-08-2017, 03:38 AM
  2. [SOLVED] VLOOKUP function not displaying results
    By B24Mike in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-25-2015, 01:09 PM
  3. Re: VLOOKUP function. results not correct/as execpted
    By Mr.Magoo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-10-2014, 06:17 AM
  4. How to create a VLOOKUP Function? -- Two Same Information BUT the results are different
    By ACCT-003 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-25-2014, 12:28 AM
  5. Vlookup results based on IF Function
    By numbersdntlie in forum Excel General
    Replies: 9
    Last Post: 12-21-2011, 01:24 PM
  6. IF function to check results of a VLOOKUP and adjust value accordingly
    By DarrenHeaphy in forum Excel General
    Replies: 3
    Last Post: 12-06-2010, 04:24 PM
  7. Worksheet function Vlookup and VLOOKUP return different results
    By zandero in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-08-2010, 08:24 AM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1