+ Reply to Thread
Results 1 to 10 of 10

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
    Microsoft 365
    Posts
    215

    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
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,134

    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
    Microsoft 365
    Posts
    215

    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
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,134

    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
    Microsoft 365
    Posts
    215

    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
    Microsoft 365
    Posts
    215

    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
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,134

    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
    Microsoft 365
    Posts
    215

    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.

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

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

    Try the following in cells F2 and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  10. #10
    Forum Contributor
    Join Date
    08-21-2010
    Location
    Bournemouth, England
    MS-Off Ver
    Microsoft 365
    Posts
    215

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

    Quote Originally Posted by JeteMc View Post
    Try the following in cells F2 and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Hi JetMc. I'm sorry I didn't reply sooner. I've since played about with the sheet and decided on abandoning the idea. Thank you for taking the time to help me.

+ 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