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

Mohammad alsaaideh

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

Kaveri Vipat

Hi Mohammad alsaaideh,

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

Mohammad alsaaideh

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

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