+ Reply to Thread
Results 1 to 7 of 7

Return horizontal and vertical values

  1. #1
    Registered User
    Join Date
    03-26-2008
    Posts
    5

    Return horizontal and vertical values

    Hi,

    In one cell I need to return topmost corresponding horizontal value of a
    table using a value from the table array.
    In another cell, I need to return left most corresponding vertical value in
    a table using a value from the table array. example

    1 2 3 4 5 6 7
    9 10 11 12 13 14 15
    16 17 18 19 20 21 22
    23 24 25 26 27 28 29

    in one cell by typing 19, I want the formula to return 4 (matching from
    topmost row), in another cell 16 (matching from leftmost column).

    Thank you!

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572
    This user defined function may work for you:
    Please Login or Register  to view this content.
    Adding the Macro (Per Leith Ross)
    1. Copy the macro above pressing the keys CTRL+C
    2. Open your workbook
    3. Press the keys ALT+F11 to open the Visual Basic Editor
    4. Press the keys ALT+I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Paste the code by pressing the keys CTRL+V
    7. Make any custom changes to the macro if needed at this time
    8. Save the Macro by pressing the keys CTRL+S
    9. Press the keys ALT+Q to exit the Editor, and return to Excel.
    ---------------------------------------------------
    Example use:
    Table values are entered in cells A1 to G4
    the test value is, say, 10
    you want a Vertical, i.e., top row of the same column put in cell I1, then
    =ParseTable(A1:G4,10,"V")
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    03-26-2008
    Posts
    5

    return horizontal and vertical values

    thank you ben,

    it worked for that range (1 through 29) but my actual range looks something like this:
    5.00 5.50 6.00 6.50 7.00 7.50
    60.50 0.9998 0.9998 0.9998 0.9998 0.9998 0.9998
    61.00 0.9996 0.9996 0.9996 0.9996 0.9996 0.9996
    61.50 0.9995 0.9995 0.9995 0.9995 0.9995 0.9995
    62.00 0.9993 0.9993 0.9993 0.9993 0.9993 0.9993
    62.50 0.9991 0.9991 0.9991 0.9991 0.9991 0.9991
    63.00 0.9989 0.9989 0.9989 0.9989 0.9989 0.9989
    63.50 0.9987 0.9987 0.9987 0.9987 0.9987 0.9987

    When I used the code for this table it didn't seem to work. also, my look up value might not alway match the number from the array and I want it to look for the closest match from the array and return corresponding row and column values.
    thank you very much!

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572
    Hi tsanodze,

    Sorry for the delay, but your second requirements were significantly different from the first. However this new code should work:
    Please Login or Register  to view this content.
    ----------------------------------------------------
    NOTES:
    The function will copy all the table values except row one and column one to a two column array.

    The first column will hold the table value and the second column will hold either the row or the column number that the value occupies in the table depending on the "H" or "V" parm entered in the function call. An "H" will mean that the value's row in the table is used, etc.

    After the two-column array is filled, I used a basic bubble sort to order the values from lowest to highest.

    After the sort, the test value is compared to the first and last entries in the sorted list. If lower than the smallest value or bigger than the largest, then the function returns a "#bounds!".

    Otherwise the lookup function is used to fetch the row or column number of the match/nearest value. Depending on the H/V parm, the function returns a value in row 1 and specified column or column 1 and the specified row of the table.

    Example:

    Please Login or Register  to view this content.


  5. #5
    Registered User
    Join Date
    03-26-2008
    Posts
    5

    thank you

    Thank ben, sorry for the delayed response. worked great!

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572
    Thanks for the feedback, glad to help.

  7. #7
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211
    Quote Originally Posted by tsanodze
    Hi,

    In one cell I need to return topmost corresponding horizontal value of a
    table using a value from the table array.
    In another cell, I need to return left most corresponding vertical value in
    a table using a value from the table array. example

    1 2 3 4 5 6 7
    9 10 11 12 13 14 15
    16 17 18 19 20 21 22
    23 24 25 26 27 28 29

    in one cell by typing 19, I want the formula to return 4 (matching from
    topmost row), in another cell 16 (matching from leftmost column).

    Thank you!
    Try this:

    For topmost
    =INDEX(A1:G1,0,MAX(INDEX((A1:G4=19)*COLUMN(A1:G4),0)))

    For leftmost
    =INDEX(A1:A4,MAX(INDEX((A1:G4=19)*ROW(A1:G4),0)))

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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