Lookup or Data Mesh for values based on 2 criteria
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?
Answers

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!

Thank you, Nick. This was great. I combined this with INDEX MATCH and it worked beautifully.
Help Article Resources
Categories
Check out the Formula Handbook template!