+ Reply to Thread
Results 1 to 8 of 8

Using vlookup to find smallest number and another condition is not quite working

  1. #1
    Forum Contributor
    Join Date
    08-21-2010
    Location
    Bournemouth, England
    MS-Off Ver
    Excel 2016
    Posts
    202

    Using vlookup to find smallest number and another condition is not quite working

    Hello.

    I want to find all instances where there is an 'A' in colum A and the smallest number in column D. In other words, both of those conditions must be true.
    The result in the first row (F2) shows only one condition to be true and returns 2.8 and I would like it to return a '0'
    The result in the second row (F3) shows both conditions as true and returns the smallest number which is correct (1.46)
    So, when both conditions are true, I want to see the smallest number in column F. Otherwise, I'd like to see a zero in column F

    Thank you.

    https://docs.google.com/spreadsheets...it?usp=sharing
    Last edited by tom hatten; 10-14-2021 at 12:18 PM.

  2. #2
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit
    Posts
    848

    Re: Using vlookup to find smallest number and another condition is not quite working

    So, what are you expecting to see in cell F5 ?

  3. #3
    Forum Contributor
    Join Date
    08-21-2010
    Location
    Bournemouth, England
    MS-Off Ver
    Excel 2016
    Posts
    202

    Re: Using vlookup to find smallest number and another condition is not quite working

    Hi.
    Zero, because the smallest number is in column C

  4. #4
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit
    Posts
    848

    Re: Using vlookup to find smallest number and another condition is not quite working

    OK...

    Can you try this in cell F2 and drag down...

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    08-21-2010
    Location
    Bournemouth, England
    MS-Off Ver
    Excel 2016
    Posts
    202

    Re: Using vlookup to find smallest number and another condition is not quite working

    That looks good! I've changed some of the letters in column A to 'P' and the results have changed accordingly.

    Thank you very much.

  6. #6
    Forum Contributor
    Join Date
    08-21-2010
    Location
    Bournemouth, England
    MS-Off Ver
    Excel 2016
    Posts
    202

    Re: Using vlookup to find smallest number and another condition is not quite working

    I didn't think this through properly.
    If column A contains an 'A' but column D does not have the lowest number, I would like an indication that it has not met the conditions.
    Perhaps the word "No", if possible?
    Examples are in cells F6 and F9

    Thank you.

  7. #7
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit
    Posts
    848

    Re: Using vlookup to find smallest number and another condition is not quite working

    Try this formula in cell F2 and drag down, where the value (-1) in Column F will indicate that there is "A" in column A but Column D is not the minimum of Columns B, C and D.

    Please Login or Register  to view this content.
    Last edited by Haluk; 10-14-2021 at 01:36 PM.

  8. #8
    Forum Contributor
    Join Date
    08-21-2010
    Location
    Bournemouth, England
    MS-Off Ver
    Excel 2016
    Posts
    202

    Re: Using vlookup to find smallest number and another condition is not quite working

    The result for cells F3 & F5 are exactly what I want.

    If column D contains the lowest number but column A does not contain an 'A', could it please show a -1 in column F? Examples are in F2 & F4
    If column D does not contain the lowest number and column A contains an 'A', then please show a '0'

    Thank you so much for your help.

+ 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. format condition with horizon number, highlight with smallest
    By leakhna in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-10-2021, 06:06 AM
  2. Formula to find the second smallest number
    By Lino 2970 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-22-2019, 01:57 PM
  3. find the smallest number
    By sanju2323 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-24-2015, 10:12 AM
  4. [SOLVED] Find the smallest number in a column that has some #N/A in it.
    By JimLau in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-22-2014, 11:02 AM
  5. [SOLVED] find smallest Number in a col
    By bnwash in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-12-2013, 10:26 PM
  6. [SOLVED] How to find the smallest number for each category
    By BNCOXUK in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-07-2013, 01:13 PM
  7. find smallest number not equal to 0
    By dave in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-20-2005, 06:06 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