Help with vlookup/index/match

Melisa Dannhauser
Melisa Dannhauser ✭✭✭
edited 04/17/20 in Formulas and Functions

Hi,

I'm trying to create a table that looks up data from a list of entries based on multiple criteria. I have been able to achieve PART of my task with different formulas but can't figure out how to achieve my desired end result. I tried Index/Match, vlookup/match but can't get it to work. I want the formula to be dynamic so I can drag it down/across.


I tried this but I would need a second criteria for the status and I'm not sure how to do that...

=VLOOKUP([Column3]2, [Column6]2:[Column11]21, MATCH([Column2]5, [Column6]2:[Column11]2, 0), false)


I also tried including COLLECT but that means I need to specify the category column I want the data pulled from which is not ideal in my case. I want the category to be part of the lookup because I will have many more categories and rows (example is very simplified).


First table on the left is my source data. Columns on the right (yellow section) is what i need populated. Here is the criteria:

In cell [column3]5, display number located in column9 based on the following criteria:

  • When I put in a site number 12345 in cell [column3]2,
  • Look for site number 12345 in column6
  • Look for Type "budget" in column7
  • Look for Status "approved" in column8
  • Look for category "General" in row 2
  • Display number in column9


I think the biggest issue I'm having is the category lookup in row 2.

Any help is appreciated. I can do this in excel with array formula but since SS doesn't do those I don't know how to build the formula to match all my criteria.



Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!