Help with vlookup/index/match
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
-
Can you spread the top row of your table out a little bit to encompass two rows so that "Approved" and "Not Approved" are on one row and "Budget" and "Invoice" are in another?
Help Article Resources
Categories
Check out the Formula Handbook template!