How i find value from table depend on the value of row & column ?

Options

Hi all ,


I have table :

and i have value user will inter .


how i can get value from table depend on the value of row & column ?

and if value not existing , for ex ( 16.3 ) its not existing in header i need to go to closest value that mean 15 ??


Thanks

Answers

  • Kaveri Vipat
    Kaveri Vipat ✭✭✭✭✭✭
    Options

    Hi Mohammad alsaaideh,

    Can you share some more details of your requirement, that would be really helpful.

    Thanks,

    Kaveri Vipat

    Senior Associate - Smartsheet Development, Ignatiuz Software

    2023 Core Product Certified

    Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"

  • Mohammad alsaaideh
    edited 07/05/23
    Options

    Hi Kaveri  ,


    i have table for ex:

    I need to find crossing cell between header & first column

    for ex: for values ( 15 & 3/4 load ) = 0.063

    i need tow things :

    first if i have value ( 16.6 & 3/4 load ) i need to find nearest value to 16.6 that 15 & find crossing cell with 3/4load .

    in excel i used :


    =,INDEX(Table2[[#Headers],[5]:[50]],MATCH(MIN(ABS(Table2[[#Headers],[5]:[50]]- Value )),ABS(Table2[[#Headers],[5]:[50]]-Value ),0)) //// this will find nearest value from header


    =INDEX(Table2[[5]:[50]],MATCH(D3,Table2[%load],0),MATCH(Value ,Table2[[#Headers],[5]:[50]],0)) //// this will find crossing cell in table .


    i hope it s clear for u ?

  • sharkasits
    sharkasits ✭✭✭✭✭
    Options

    I think the formula for this gets pretty complicated in Smartsheet, so I would us a helper column to indicate what value it will match on.

    Formula for Helper Column:

    =IFERROR(IF(MATCH([Entered Value]@row, {Headers}, 0) > 0, [Entered Value]@row, "Issue"), IF(([Entered Value]@row - MAX(COLLECT({Headers}, {Headers}, <[Entered Value]@row))) < (MIN(COLLECT({Headers},{Headers}, > [Entered Value]@row)) - [Entered Value]@row), MAX(COLLECT({Headers}, {Headers}, <[Entered Value]@row)), MIN(COLLECT({Headers}, {Headers}, > [Entered Value]@row))))


    To Get the Value:

    =INDEX({Full Load Table},Match([Load fraction]@row,{First Column},0),Match([Helper Column],{Headers},0))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!