Lookup or Data Mesh for values based on 2 criteria

Nicole D

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?

Nick Korna

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

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