+ Reply to Thread
Results 1 to 16 of 16

Search for a value or somthing immediately lesser and return its sl.no

  1. #1
    Registered User
    Join Date
    10-18-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    67

    Question Search for a value or somthing immediately lesser and return its sl.no

    I have a value, Say 22.135.
    I have two Columns, A & B. "A" has sl.no in it. "B" has random values in an unsorted fashion.
    I want to search for this value 22.135 and return it's corresponding sl.no.
    If that value doesn't exist, I have to return the sl.no of a value immediately lesser than 22.135.
    Say, a lesser value than 22.135 be, 22.132 and it occurs multiple times in various places in column B, then I have to return the sl.no of it's first occurence.

    Urgent, need it for my project.
    Last edited by projectatpel; 10-19-2013 at 05:18 PM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Search for a value or somthing immediately lesser and return its sl.no

    You should use the Vlookup function. Look at this link for further info.

    http://www.techonthenet.com/excel/formulas/vlookup.php
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    10-18-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    67

    Re: Search for a value or somthing immediately lesser and return its sl.no

    But that requires the data to be sorted in ascending order, and mine is an unsorted list
    I shouldn't sort it either.
    Quote Originally Posted by alansidman View Post
    You should use the Vlookup function. Look at this link for further info.

    http://www.techonthenet.com/excel/formulas/vlookup.php

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Search for a value or somthing immediately lesser and return its sl.no

    You are correct. Not sure if you can do it with an Index/Match functionality. You might want to try that, but that also may require a sorting in ascending order.

    What is the issue with not sorting the data? If you have unique identifier for each record, you could sort by the needed field and then resort back using the unique identifier

  5. #5
    Registered User
    Join Date
    10-18-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    67

    Re: Search for a value or somthing immediately lesser and return its sl.no

    This has gone completely above my head. IDk what is a unique identifier.
    I don't want to sort the data, coz it has to generate a graph for me. and also the input is generated from a machine, so i prefer keeping it unsorted.
    Quote Originally Posted by alansidman View Post
    You are correct. Not sure if you can do it with an Index/Match functionality. You might want to try that, but that also may require a sorting in ascending order.

    What is the issue with not sorting the data? If you have unique identifier for each record, you could sort by the needed field and then resort back using the unique identifier

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Search for a value or somthing immediately lesser and return its sl.no

    Insert a new column A. In that column insert sequential numbers 1 up. This becomes your unique identifier. Sort your data as necessary. Run your Vlookup. Resort your data based upon Column A and run your graphs. Just a thought until someone else comes along with a better solution.

  7. #7
    Registered User
    Join Date
    10-18-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    67

    Re: Search for a value or somthing immediately lesser and return its sl.no

    my column A already has sl.nos in ascending order. it is only column B that is random, but is it possible to Sort, find and unsort automatically using any formula or function, rather than manually doing it every time?

    Because I am talking about 10000 values to deal with and at least 50 such cases everyday

    Quote Originally Posted by alansidman View Post
    Insert a new column A. In that column insert sequential numbers 1 up. This becomes your unique identifier. Sort your data as necessary. Run your Vlookup. Resort your data based upon Column A and run your graphs. Just a thought until someone else comes along with a better solution.

  8. #8
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: Search for a value or somthing immediately lesser and return its sl.no

    Try something like this


    A
    B
    C
    D
    E
    1
    Sl No.
    Value
    Search Value
    Sl No.
    2
    1
    23,134
    25,156
    2
    3
    2
    25,156
    22,135
    4
    4
    3
    24,999
    5
    4
    22,132
    6
    5
    22,132
    7
    6
    22,789
    8
    7
    26,789
    9
    8
    26,789
    10
    9
    22,132
    11
    10
    24,555


    Array formula in E2 copied down
    =IF(ISNUMBER(MATCH(D2,B:B,0)),INDEX(A:A,MATCH(D2,B:B,0)),INDEX($A$2:$A$1000,MIN(IF($B$2:$B$1000<D2,ROW($B$2:$B$1000)-ROW($B$2)+1))))

    confirmed with Ctrl+Shift+Enter simultaneously
    (hold down Ctrl and Shift keys and hit Enter)

    M.
    Marcelo Branco

  9. #9
    Registered User
    Join Date
    10-18-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    67

    Re: Search for a value or somthing immediately lesser and return its sl.no

    It isn't working for all values
    if a value is immediately small, it works but a bit greater difference, it failed
    It had to return 7, but it returned 1

    Ps see: File attached herein

    Error.jpg

    Quote Originally Posted by mlcb View Post
    Try something like this


    A
    B
    C
    D
    E
    1
    Sl No.
    Value
    Search Value
    Sl No.
    2
    1
    23,134
    25,156
    2
    3
    2
    25,156
    22,135
    4
    4
    3
    24,999
    5
    4
    22,132
    6
    5
    22,132
    7
    6
    22,789
    8
    7
    26,789
    9
    8
    26,789
    10
    9
    22,132
    11
    10
    24,555


    Array formula in E2 copied down
    =IF(ISNUMBER(MATCH(D2,B:B,0)),INDEX(A:A,MATCH(D2,B:B,0)),INDEX($A$2:$A$1000,MIN(IF($B$2:$B$1000<D2,ROW($B$2:$B$1000)-ROW($B$2)+1))))

    confirmed with Ctrl+Shift+Enter simultaneously
    (hold down Ctrl and Shift keys and hit Enter)

    M.

  10. #10
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: Search for a value or somthing immediately lesser and return its sl.no

    New version

    More robust, to avoid
    http://support.microsoft.com/kb/78113

    Array formula in E2 copied down
    =IF(ISNUMBER(MATCH(ROUND(D2,3),ROUND($B$2:$B$1000,3),0)),INDEX($A$2:$A$1000,MATCH(ROUND(D2,3),ROUND($B$2:$B$1000,3),0)),INDEX($A$2:$A$1000,MIN(IF($B$2:$B$1000<D2,ROW($B$2:$B$1000)-ROW($B$2)+1))))

    confirmed with Ctrl+Shift+Enter simultaneously

  11. #11
    Registered User
    Join Date
    10-18-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    67

    Re: Search for a value or somthing immediately lesser and return its sl.no

    same error with the new version also! i've been trying to workout since morning, and wasn't able to figure out.

  12. #12
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: Search for a value or somthing immediately lesser and return its sl.no

    Quote Originally Posted by projectatpel View Post
    same error with the new version also! i've been trying to workout since morning, and wasn't able to figure out.
    Sorry, I misunderstood your problem

    Try this


    A
    B
    C
    D
    E
    1
    Sl No.
    Value
    Search Value
    Sl No.
    2
    1
    22
    999
    5
    3
    2
    23
    67
    9
    4
    3
    22
    24
    2
    5
    4
    10
    22,1
    1
    6
    5
    987
    7
    6
    9
    8
    7
    987
    9
    8
    0
    10
    9
    67
    11
    10
    56


    Array formula in E2 copied down
    =IF(ISNUMBER(MATCH(ROUND(D2,3),ROUND($B$2:$B$11,3),0)),INDEX($A$2:$A$11,MATCH(ROUND(D2,3),ROUND($B$2:$B$11,3),0)),INDEX($A$2:$A$11,MATCH(MIN(IF(B2:B11<>"",IF(D2>$B$2:$B$11,D2-$B$2:$B$11))),IF($B$2:$B$11<>"",IF(D2>$B$2:$B$11,D2-$B$2:$B$11)),0)))

    Ctrl+Shift+Enter

    M.

  13. #13
    Registered User
    Join Date
    10-18-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    67

    Re: Search for a value or somthing immediately lesser and return its sl.no

    Works like a magic! Thanx a lot

    Quote Originally Posted by mlcb View Post
    Sorry, I misunderstood your problem

    Try this


    A
    B
    C
    D
    E
    1
    Sl No.
    Value
    Search Value
    Sl No.
    2
    1
    22
    999
    5
    3
    2
    23
    67
    9
    4
    3
    22
    24
    2
    5
    4
    10
    22,1
    1
    6
    5
    987
    7
    6
    9
    8
    7
    987
    9
    8
    0
    10
    9
    67
    11
    10
    56


    Array formula in E2 copied down
    =IF(ISNUMBER(MATCH(ROUND(D2,3),ROUND($B$2:$B$11,3),0)),INDEX($A$2:$A$11,MATCH(ROUND(D2,3),ROUND($B$2:$B$11,3),0)),INDEX($A$2:$A$11,MATCH(MIN(IF(B2:B11<>"",IF(D2>$B$2:$B$11,D2-$B$2:$B$11))),IF($B$2:$B$11<>"",IF(D2>$B$2:$B$11,D2-$B$2:$B$11)),0)))

    Ctrl+Shift+Enter

    M.

  14. #14
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Search for a value or somthing immediately lesser and return its sl.no

    you can use a helper column well 2
    see attached
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  15. #15
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: Search for a value or somthing immediately lesser and return its sl.no

    Quote Originally Posted by projectatpel View Post
    Works like a magic! Thanx a lot
    You are welcome and thanks for the feedback.

    M.

  16. #16
    Registered User
    Join Date
    10-18-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    67

    Re: Search for a value or somthing immediately lesser and return its sl.no

    This is working as far as the search is concerned, but it is returning the last occurrence of the value or it's nearest, in the list.
    It has to return the first occurrence. BTW thanx for the help. post prior to this one solves my case exactly.

    I.E. one by a user @"mlcb"

    =IF(ISNUMBER(MATCH(ROUND(D2,3),ROUND($B$2:$B$11,3),0)),INDEX($A$2:$A$11,MATCH(ROUND(D2,3),ROUND($B$2:$B$11,3),0)),INDEX($A$2:$A$11,MATCH(MIN(IF(B2:B11<>"",IF(D2>$B$2:$B$11,D2-$B$2:$B$11))),IF($B$2:$B$11<>"",IF(D2>$B$2:$B$11,D2-$B$2:$B$11)),0)))

    works too fine!

    Quote Originally Posted by martindwilson View Post
    you can use a helper column well 2
    see attached

+ 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. Excel 2008 : Help with somthing Complicated
    By LAIN1987 in forum Excel General
    Replies: 4
    Last Post: 10-14-2010, 03:16 PM
  2. [SOLVED] how do I do a formuls in excel such as (if a1=somthing then b1=a .
    By Brett in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-26-2010, 03:41 AM
  3. Compare two Cells, Output somthing from a cell in the same row.
    By EvanNoble in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-09-2008, 09:31 AM
  4. Finding current week and if any item has somthing due
    By Robert Brown in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-19-2006, 09:20 AM
  5. Two Criteria with the COUNTIF function or somthing else?
    By Robert Brown in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-02-2006, 07:30 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1