Using Excel's INDEX Function with COLUMN and M

问题:

I am trying to use an INDEX function combined with MATCH, COLUMN, and a Data Validated Cell.

This is the function I have written, but it throws there was an error that there is something wrong.

=INDEX(ShipsData,MATCH(B3,ShipsData[Name],0),COLUMN(ShipsData[Max_Level]))

This works, but it is not as future proof as I would like.

=INDEX(ShipsData,MATCH(B3,ShipsData[Name],0),13)

I should point out neither of these works as the maximum for a data validation between condition, the second works when placed in a cell.

Additional Information:

ShipsData is the name of the table, B3 is a Data Validated cell with a drop down of all possible ships, and there are Two columns in the table named Name and Max_Level.

What I am trying to do is validate the level of a ship so that it is between (inclusive) it's minimum level (1) and it's maximum level which varies per ship.

If you need any more information I can provide it.

Edit:

I solved part of the problem, the formula now completes in a cell, there was a typo in the table header Max_Level.

However when I past this formula into the data validation, it gives me a message about 'We found a problem with this formula' and it will not insert, however the function works in a cell now.

回答1:

On my system, your formula shows a #REF error.

Your problem in the first instance is that the Column argument for the INDEX function is looking for the column number in the table; and your reference is returning the absolute column number of the Max_Level column. So this will work if and only if the table starts in column 1 (column A).

Try this:

=INDEX(ShipsData,MATCH(B3,ShipsData[Name],0),COLUMN(ShipsData[Max_Level])-COLUMN(ShipsData)+1)

If your formula is showing "The formula you typed contains an error", then it may be that you have moved something around such that Max_Level (or Name) is no longer a part of the Table.

You can check this by, in some blank cell, typing:

=ShipsData[

and seeing the column headers available showing up in the dropdown.

e.g:

回答2:

Custom Data Validation for cell A1, assuming Name and Max_Level (excluding their headers) occupy the ranges E2:E10 and F2:F10 respectively :

=AND(A1>=1,A1<=INDEX($F$2:$F$10,MATCH($B$3,$E$2:$E$10,0)))

If you prefer to stay with Structured Table References, which is of course more dynamic, then you will first need to go to Name Manager (Formulas tab) and define Limit as:

=INDEX(ShipsData[Max_Level],MATCH(Sheet5!$B$3,ShipsData[Name],0))

after which the above Custom Data Validation becomes:

=AND(A1>=1,A1<=Limit)

Regards