Help with cross sheet formulas

So you are trying to add up all costs for Wilson Crossing for Floor Plan 1, Floor Plan 2, Floor Plan 3, etc... individually?

Did you get it working how you wanted?

Best,

Andrée

Is there any part in particular you need help with? I have found that when doing an INDEX/MATCH where multiple columns are used for verification, it is sometimes easier to use a helper column on each sheet along with a JOIN function. That way you can have a specific string of text that combines both into a unique value that can be used in your MATCH function as opposed to having nested INDEX/MATCH statements which gets to being a royal pain.

It's all starting to come together now. Ok. First I am going to suggest making sure all properties and unit names are entered exactly the same across all sheets. For example, your property name in the first screenshot is "Wilson Crossing Apartments", but in the second screenshot it is just "Wilson Crossing". This can be accounted for, but opens up an additional margin of error which can grow exponentially the more inconsistencies there are. I will show examples of  the recommended as your current setup could get rather convoluted.

Here's what we will be using...

=INDEX(range you want to pull data from, row reference number, column reference number)

=MATCH(data you want to search for, range you want to search in, type of match)

.

The INDEX function displays data from a range using a row number and an option column number to determine the specific cell within the range.

The MATCH function will display a number that designates which cell within a set range the data you want to search for is found.

.

We want to pull data (INDEX function) that can be anywhere from the first Floor Plan # column to the last Cost # column and can also be anywhere from row 1 to row 5,000 (max number of rows in a sheet). We will designate this range as Range X.

.

So to use your screenshots above (assuming Wilson Crossing is on row 1 of the master sheet for simplicity)...

We are looking at your entire sheet from the first [Floor Plan #] column all the way over to the last [Cost #] column and from row 1 all the way down to the very last row.

.

We see that Wilson Crossing is on Row 1, and the Woodland floor plan is in the first column. We need the second column though to pull the Cost associated. Now that we know the row and column number (1 and 2) we can use an INDEX function to display the data.

The basic INDEX function would look like this:

=INDEX({Master Sheet Range X}, 1, 2)

.

But we can use the MATCH function to automate the row and column numbers for the INDEX function like so:

.

MATCH($[Property Info]@row, {Master Sheet Property Name}, 0) <--- Returns a 1 for the row number

MATCH($[Floor Plan]@row, {Master Sheet Range X}, 0) <--- Also returns a 1 for the column number

We then add 1 to the column number to give us the column AFTER the floor plan name which puts us in the corresponding Cost # column which brings us to our solution...

.

=INDEX({Master Sheet Range X}, MATCH($[Property Info]@row, {Master Sheet Property Name}, 0), MATCH($[Floor Plan]@row, {Master Sheet Range X}, 0) + 1)

.

{Master Sheet Range X}: Use the appropriate steps for cross sheet referencing and select ALL columns on your Master sheet from the first Floor Plan # column to the final Cost # column.

$[Property Info]@row: Leave as is. Refers to the [Property Info] column on your formula sheet for whatever row the formula is on.

{Master Sheet Property Name}: Cross sheet reference to the Master sheet selecting the column where the property name is housed.

$[Floor Plan]@row: Same as [Property Info]@row except referring to the [Property Info] column.