+ Reply to Thread
Results 1 to 4 of 4

Formula to retrieve specific data if certain conditions are fulfilled

  1. #1
    Registered User
    Join Date
    02-05-2016
    Location
    Kigali
    MS-Off Ver
    excel 2007
    Posts
    21

    Formula to retrieve specific data if certain conditions are fulfilled

    Hi guys,

    I have data in column A & B that are related and constant. Then I have data in cells D2 & D3 that will be changing periodically.
    I want cells E2 & E3 to give me a value from column B that equals the value I have in cells D2 & D3 if there is any. In case there isn't any, I want cells E2 & E3 to give me the closest bigger value from column B. For example in my workbook, D2=42, since there's no 42 in column B, E2 will be equal to 52. D3=61, since there is 61 in column B, E3 will be equal to 61.
    I also want cells F2 & F3 to give me the respective values from column A that are related to the values chosen from column B. In my workbook, 52 is related to 17 and 61 is related to 19.
    What formulas can I use for cells E2, E3, F2 and F3?

    Thanks for your help.

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,863

    Re: Formula to retrieve specific data if certain conditions are fulfilled

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

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

    and copy both cells down
    Best Regards,

    Kaper

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,742

    Re: Formula to retrieve specific data if certain conditions are fulfilled

    In E2

    =IFERROR(INDEX(Sheet1!$B$2:$B$22,MATCH($D2,$B$2:$B$22,0)),INDEX(Sheet1!$B$2:$B$22,MATCH($D2,$B$2:$B$22,1)+1))

    in F2

    =INDEX($A$2:$A$22,MATCH($E2,$B$2:$B$22,0))

    Copy both down

  4. #4
    Registered User
    Join Date
    02-05-2016
    Location
    Kigali
    MS-Off Ver
    excel 2007
    Posts
    21

    Re: Formula to retrieve specific data if certain conditions are fulfilled

    Thanks guys, both your formulas work perfectly.

+ 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] Attribute a specific number to a specific cell if conditions are fulfilled
    By joel.mugabe in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-05-2016, 10:16 AM
  2. Replies: 2
    Last Post: 01-14-2016, 03:53 PM
  3. Replies: 4
    Last Post: 01-02-2015, 01:33 PM
  4. [SOLVED] macro, retrieve data to specific columns
    By Leoparddd in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-23-2014, 11:22 AM
  5. Retrieve data from specific sheets
    By danj in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-19-2011, 05:04 PM
  6. Retrieve specific data - challenging
    By peter1 in forum Excel General
    Replies: 2
    Last Post: 08-20-2010, 09:21 AM
  7. Replies: 1
    Last Post: 03-05-2010, 03: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