Help with vlookup/index/match

Melisa Dannhauser

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.

Paul Newcome

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?