Grid Index/V-lookup Help

Hi,

I am hoping someone can help me with a vlookup/index formula (or a different formula if someone knows of something that works better for the following example)...

I'm creating a tool for modeling and have a grid that I'm referencing for a master modeling tab. The grid contains multiple districts. Each district will have different levels based on the office locations employee size. The estimated budget for each office location can be found by the number of employees in the office.

I then have another tab where the modeling for all of the office locations takes place. I need a formula that finds the budget for each office location based on their employee size. The problem with Indexing or Vlookups is that it's asking for specific column numbers. I don't have specific column numbers to reference. I'm trying to find the district that the office location is in and the column reference will be the # of employees that the modeling tab matches to. (for example, the office location is assigned to a certain district - that district has its own budget levels based on employee size: 1, 50, 100, 150, 200, 250. If the office location has 140 employees, it would match to the 100 employees level - not 150 as it has not hit that level of employees yet).

Sorry if this is really confusing.. i'm at a loss with what to do. I have attached an example spreadsheet with everything all in on tab. The inputs are in the table to the right and the "modeling" is the table to the left. The yellow cells under "Budget" is what i am looking for.

Try this in N5 then copy down:

Please Login or Register  to view this content.

WBD

Or, avoiding the volatile OFFSET, you could do it like this:

Please Login or Register  to view this content.

WBD

Sorry I didn't realize you posted another reply. The offset formula worked perfectly but the second with with indexes did not work and gave me incorrect results.. Why would the OFFSET formula be volatile?

I Annotation 2020-01-09 102150.png

This is the result of the other one - maybe i did something wrong?

Originally Posted by egren

Sorry I didn't realize you posted another reply. The offset formula worked perfectly but the second with with indexes did not work and gave me incorrect results.. Why would the OFFSET formula be volatile?

I Attachment 657126

This is the result of the other one - maybe i did something wrong?

Yep!

Looks like you've anchored the formula to the value in $M$5 so it's using 130 as the number to lookup resulting in 15000 budget in the Milwaukee district. I would recommend copying the exact formula I posted as using the wildcard is also going to be a little more reliable:

Please Login or Register  to view this content.

WBD

See this article about volatile functions: https://www.vertex42.com/blog/excel-...functions.html

Basically, any formula using OFFSET will recalculate when any cell is changed on the sheet. This could make the formula slow or slow down your sheet.

WBD