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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!