Index/match sort issue

Hi
I have reviewed the posts involving the index/match and sort difficulties.
I have tried the forum suggestions, but it's not working for my spreadsheet which has to be sorted continually.
I have attached the spreadsheet for review.
The formula in question is in COLUMN M, highlighted in pink.
It is referencing another page that is only giving back a base salary, that is tab is BUILDCONTRACTS.
I didn't include that tab because it has way too much sensitive information.
In COL D of the attached spreadsheet I deleted sensitive name information, so there's just a number there due to referencing issues.
This is the formula
=INDEX(BuildContracts!$F$8:$F$173,MATCH(BuildAssignments!E79,BuildContracts!$A$8:$A$173,0))/210*16*0.4+(3000*0.4)

Thank you so much,
Jules

Jules,

Your formula has missed out a few apostrophes. Try the attached:

=INDEX('Build Contracts.xls'!$F$8:$F$173,MATCH(E79,'Build Contracts.xls'!$A$8:$A$173,0))/210*16*0.4+(3000*0.4)

(You don't need "BuildAssignments!" for E79, because the formula is on that page already)

Hope this helps

Ochimus

Jules,

Not sure what you mean by "original" data?

If you can explain which column you "sort", and what should appear in Col M when you do, then hopefully we can sort this out.

Because whether you use Index/Match or VLOOKUP, the only reason your formulae should generate a "N/A" in Col M is if there is no match in Build Contracts A8:A173 for whatever is in Col E of the Build Assignments sheet, or there is no value in Build Contracts F8:F173 against the row.

The first problem is that in your attachment most of column M has no formula.

Some are a fixed number (e.g. M14 is 616) so will not change if you resort other columns.

Some are multiplying a fixed number (e.g. M43 is 1211*1.03), and again will not change if you resort other columns

Others are matching a value from a fixed range in Col F of the "BuildContracts' sheet, so they will change if you resort Col E.

But you then divide the number by widely different formulae:
M62 divides it by 180*6
M79 divides it by 210*16*0.4+(3000*0.4)
M80 divides it by 210*16*0.15+(3000*0.15)
M113 divides it by 210*16+3000


Ochimus

Still not really clear on this.

1. What are the Column headings in the Build Contracts sheet?

2. Col E on the BuildAssignment sheet contains 325 "unique" serial numbers.
Why is your formula looking for only 165 numbers in the Build Contracts sheet? (Your formula says it searches rows 8 - 173 )?
Why are there only 11 in your Build Contracts atttachment?
Those 11 serials are in rows 2 - 13, which means your formula is not looking at anything in rows 2 - 7, and will not find any data in rows 14 - 173, because they are blank on your attachment.

3. What column on the BuildAssignment sheet do you use to "sort" the data?

4. Does the name in Col D of the Build Assignments determine whether Col M gets the entire supplemental pay, or just a certain percentage?

Ochimus

Jules,

Appreciate the much clearer explanation.

Hopefully the attached sheet will show the answer to your problem.

"Build Contract" sheet

Col A contains eleven serial numbers from your list.

Cols B - E show their different Base Salaries (as I'm not clear whether everyone has the same in your file or they are individual?), and up to three allowances. You can see some get all the allowances, some get two, and some get only one.

Cols G - K then break down the percentages of their pay between four Cost Codes.

"Build Assignments" sheet:

Col F contains 327 Serials. To "mimic" your sort issue, the order they appear changes every time you highlight any cell on the sheet and press "Copy". (Uses an Index/Link to the Random Generator sheet)

If the Serial matches one of your eleven, then the Base Salary will appear in Col M under the following formula starting in row 3 and copied down:

=IFERROR(INDEX('Build Contracts'!$B$3:$B$13,MATCH(E3,'Build Contracts'!$A$3:$A$13,0)),"")

(If the serial in Col E does not match any of the values in A3 - A13 in the Build Contracts sheet, then Col M stays blank,
If it does match, then find the matching value in B3 - B13 of the Build Contracts sheet and copy it into Col M.)

If there is a match, it then adds the Allowances into Cols N - P under this formula, starting in row 3 and copied across and down:

=IFERROR(INDEX('Build Contracts'!$C$3:$C$13,MATCH(E3,'Build Contracts'!$A$3:$A$13,0))*M3,"")

(If the serial in Col E does not match any of the values in B3 - B13 in the Build Contracts sheet, then Col N stays blank,
If it does match, then divide the salary in Col M by the value from Col C of the Build Contracts sheet.
If the allowance does not apply to that serial, the cell stays blank.)

Cols R - V then break down the Base Salary + any allowances between the four Cost Centres by the values you set in the Build Contract sheet using the following formula copied across and down:

=IFERROR(INDEX('Build Contracts'!$G$3:$G$13,MATCH(E3,'Build Contracts'!$A$3:$A$13,0))/100*SUM(M3:P3),"")

(If the serial in Col E does not match any of the values in B3 - B13 in the Build Contracts sheet, then Col R stays blank,
If it does match, then multiply the combined salary and allowances in Cols M - P by whatever percentage you had in Col G of the Build Contracts sheet.

Hope you use that and "adapt" it to your actual filepaths and ranges.

Ochimus