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.
