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


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 ??



  • Kaveri Vipat
    Kaveri Vipat ✭✭✭✭✭✭

    Hi Mohammad alsaaideh,

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


    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

    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 ✭✭✭✭✭

    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!