Lookup Table Question

michael tomlinson
michael tomlinson ✭✭✭
edited 12/09/19 in Formulas and Functions

I'm trying to figure out a way to apply my vlookup or index/match combo as a dynamic formula across my sheet.  I have a set cell that I can use as the beginning of my array of cells to reference but I want the formula to update the final cell based upon the row I'm at minus 1.  

There may be a much better way of handling this but I'm not finding it and would love feedback on more intelligent builds if anyone has an idea.

Formula in test is:

=VLOOKUP([Parent UID]3, [Parent UID]3:[Column3]@row-1, 2, false)

Screenshot provided.

Thanks a ton!

Mike

smartsheetvlookup.PNG

Tags:

Comments

  • If anyone else runs into this issue in the future.  I solved it by using an index/match using a static variable in the first portion of the formula and a non static variable in the second to allow for incrementing.

     

    =INDEX([Primary Column]$1:[Primary Column]4, MATCH([Parent UID]@row, [Parent UID]$1:[Parent UID]4))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!