Lookup or Data Mesh for values based on 2 criteria

Options

I have a sheet that I'd like for it to enter a value based on 2 criteria, Site and Job. John Doe gets hired for Job 3 at Site 8 ($131,040).

Is there a formula for Smartsheet to enter the value based on column and row data? Or is there a way to set up Data Mesh to lookup this way?

Tags:

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    Hi @Nicole D ,

    You can do this with some nested IF statements.

    Inputting your data into a sheet for reference and then using a cross sheet reference would give the following formula that you can then use:

    =IF(Job@row = "1", VLOOKUP(Site@row, {Site & Job ref Range 1}, 2, false), IF(Job@row = "2", VLOOKUP(Site@row, {Site & Job ref Range 1}, 3, false), IF(Job@row = "3", VLOOKUP(Site@row, {Site & Job ref Range 1}, 4, false), IF(Job@row = "4", VLOOKUP(Site@row, {Site & Job ref Range 1}, 5, false)))))

    The {Site & Job ref Range 1} window would look like this:

    The sheet for inputting names etc. would then look like this:

    If you preferred to have the data on the same sheet as the inputs (such as hiding the reference columns) then you could use something like the following:

    With the formula in the Pay column being:

    =IF([Job2]@row = 1, VLOOKUP([Site2]@row, Site:[Job 4], 2, false), IF([Job2]@row = 2, VLOOKUP([Site2]@row, Site:[Job 4], 3, false), IF([Job2]@row = 3, VLOOKUP([Site2]@row, Site:[Job 4], 4, false), IF([Job2]@row = 4, VLOOKUP([Site2]@row, Site:[Job 4], 5, false)))))

    Hopefully this helps!

  • Nicole D
    Nicole D ✭✭✭✭
    Options

    Thank you, Nick. This was great. I combined this with INDEX MATCH and it worked beautifully.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!