+ Reply to Thread
Results 1 to 7 of 7

If value in table (or nearest value) return column and row

  1. #1
    Registered User
    Join Date
    10-11-2012
    Location
    egypt
    MS-Off Ver
    Excel 2013
    Posts
    33

    If value in table (or nearest value) return column and row

    hi ,

    could you please help me to find a formula for the below

    this is my data
    a b c d e f g h
    1 2 3 4 5 6 7 8 9
    2 11 13 15 17 19 21 23 25
    3 28 31 34 37 40 43 46 49
    4 53 57 61 65 69 73 77 81
    5 86 91 96 101 106 111 116 121
    6 127 133 139 145 151 157 163 169


    And i want formula if a value is there in the table then return the column and the row
    if the value is not there find the nearest value to it and return the column and row.

    Example 1
    Column Row
    cell input 31 Result 31 b 3


    Example 2
    Column Row
    cell input 100 Result 101 d 5


    note 31 is there in the table
    100 is not in the table.


    thanks in advance.
    Last edited by Pepe Le Mokko; 04-17-2020 at 07:48 AM. Reason: Title

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: If value in table (or nearest value) return column and row

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    As you are new I did it for you this time.Please read forum rules

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: If value in table (or nearest value) return column and row

    Administrative note

    Welcome to the forum

    in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

    Please take a moment to read it and attach a sheet accordingly.

    Thanks you for helping us help you

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: If value in table (or nearest value) return column and row

    Administrative note

    Still using XL2003 as indicated in your profile?
    If not,perhaps update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to? Members tailor answers based on your Excel version.
    Thanks

  5. #5
    Registered User
    Join Date
    10-11-2012
    Location
    egypt
    MS-Off Ver
    Excel 2013
    Posts
    33

    Re: If value in table (or nearest value) return column and row

    sorry for that - finally i attach an excel sheet.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: If value in table (or nearest value) return column and row

    Hi,

    I applied the formulas in cells G16 to return result:

    =IF(MAX(IF($B$2:$I$10=$D16,$B$2:$I$10))=$D16,$D16,IF(ABS(MAX(IF($B$2:$I$10<$D16,$B$2:$I$10)-$D16))<ABS(MIN(IF($B$2:$I$10>$D16,$B$2:$I$10))-$D16),MAX(IF($B$2:$I$10<$D16,$B$2:$I$10)),MIN(IF($B$2:$I$10>$D16,$B$2:$I$10))))

    cell H16 for the column:
    =INDEX($A$1:$I$1,MAX(IF($A$1:$I$10=$G16,COLUMN($A$1:$I$10))))

    cell I16 for the row :
    =INDEX($A$1:$I$1,MAX(IF($A$1:$I$10=$G16,COLUMN($A$1:$I$10))))

    All the formulas are array type. so you should pree CTRL+SHIFT+ENTER to make it work instead of just "Enter".

    You can drag down each formula only up and down in same column, since each formula is different than the other
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    10-11-2012
    Location
    egypt
    MS-Off Ver
    Excel 2013
    Posts
    33

    Re: If value in table (or nearest value) return column and row

    this is what im looking for , much appreciated.

+ 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] Search exact word within a string - SEARCH formula help
    By chullan88 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-23-2016, 06:11 PM
  2. [SOLVED] extend search formula to mutiple search for single a cell
    By lagiosman in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-20-2014, 01:16 PM
  3. VBA Code - Search text & search number & search qty and result - Urgent Please
    By naresh73 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-15-2014, 10:51 AM
  4. [SOLVED] How to ask formula to search for multiple values within one search?
    By Buzz57 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-04-2014, 11:14 PM
  5. Modify this Search Formula to search across a range of cells
    By zicitron in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-16-2013, 08:11 AM
  6. Formula to search, match value, search again, return SUM
    By Russ Fuquay in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-26-2013, 11:01 PM
  7. Replies: 5
    Last Post: 03-29-2012, 04:12 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