Hi. In the attached workbook, the first sheet has a formula that pulls values from the second sheet according to the set criteria through the INDEX MATCH functions. My problem is that when I converted the source data into a table, the formula returned error results. The column headings which are numbers were turned into text. I tried formatting the headings into numbers and general but still the errors remain. Typing an apostrophe before the number in the first sheet just so the errors would be eliminated is incorrect.


The temporary workaround is to exclude the column headings in the table but the data source looks ugly.
Any idea on how to straighten the column headings?
Thank you.

Attached Files

SalarySched.xlsx (33.4 KB, 7 views)

=INDEX(Table2_Salary,MATCH([@[SALARY GRADE]],Table2_SG),MATCH([@[SALARY STEP]],Table2_Step+0))

Just add zero to the heading to make it a number

Dont know why it did it though

Attached Files

SalarySched_Purfleet.xlsx (34.0 KB, 6 views)

Hi

• table headings are always converted to text
• once again, formatting has nothing to do with the underlying values, it is the way XL shows us the data
• while building your sheets DO NOT use manual column alignment. It looks nice but hides the fact that the cell content is text or number . Apply this at the finishing stage

And the other approach would be =INDEX(Table2_Salary;MATCH([@[SALARY GRADE]];Table2_SG);MATCH([@[SALARY STEP]]&"";Table2_Step))

Thank you!
Both formulae work perfectly in my actual data where the INDEX MATCH are nested in an IF formula.
Your advice Pecoflyer is well taken and noted.

You're welcome and thanks for the rep

Headers for defined Tables can't be numbers (or formulas).

And for your case, your solution is too complex!

Attached Files

SalarySchedNew.xlsx (35.9 KB, 8 views)

Thank you Arvil.
I didn't know that there is a much simpler formula to get the salary from a given matrix. i will post in a new thread a related problem.

[And for your case, your solution is too complex![/QUOTE]
Arvil may I ask about the function of the extra comma after tAnnex?

I assume the question is about formula in SALARY column of Table tAnnex.

tAnnex in formula returns datarange of Table tAnnex, i.e. a range A2 : D5 in example file. NB! This range is 2-dimensional, i.e. in general it contains rows and columns.
For 2-dimensional ranges/array:
INDEX(DataRange,RowNo,ColumnNo), returns a value from single cell;
INDEX(RowNo,), returns a single row from datarange;
INDEX(,RowNo), returns a single column from datarange;

As follows, 'INDEX(tAnnex,,[@[SALARY STEP]]+1)' returns a datarange column ([@[SALARY STEP]]+1 from tAnnex, i.e. when SALARY STEP in tSalaries is 1, the column Step1 of tAnnex is returned, when SALARY STEP is 2, the column Step2 is returned, etc.

But why are there two commas in the formula?
=INDEX(tAnnex,,
I tried deleting one comma but got error result.

The formula has 3 parameters: Range, RowNo, and ColumnNo. As it must return all rows of return column, the parameter RownNo must be undetermined - what is done leaving the 2n'd parameter out, but keeping the delimiter for it present.

When you delete one of commas, you change the INDEX() formula to one with 1-dimensional range as 1st parameter.

Got it but quite. Thank you for the detailed explanation Arvil.