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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!