INDIRECT Function for Two-Dimensional Lookup

[IMA_Saihat]

Hi,
The Excel INDIRECT function converts a text string into a cell reference, we want to use this advantage of INDIRECT function for Two-Dimensional Lookup as explained in the following example:

We have merit increases table that based on the annual performance rating of the employee and the position of his/her salary in the salary scale, we are aiming to get the merit increase percentage due to each employee by taking the following steps:

Step (1):
Select the Merit percentages Table and go to the Formula Tab – Select - Create from Selection - to name all rows and columns of the Merit percentages Table.



Step (2)
You can see The Naming Result by selecting Formulas Tab – Name Manager:



Step (3):
Write the Indirect Function in Cells D10, D11, D12

=INDIRECT(B10) INDIRECT(C10)





Attachment: Indirect.xlsx

[MarvinP]

Hi IMA_Saihat,

This topic is very similar to Cascading Dependent Dropdowns as explained on:
https://www.ablebits.com/office-addi...n-lists-excel/

Good explanation of your problem and an answer! Thanks for sharing it.

[verno74]

Is it possible to use this function if numbers are used as names?

[IMA_Saihat]

Hi, verno74

Rules for Creating Names

An Excel name can't contain space characters, and there are other rules to follow when you're creating a name.
•The first character of a name must be a ◦letter
◦underscore (_)
◦backslash (\).

•Remaining characters in the name can be ◦letters
◦numbers
◦periods
◦underscore characters

•Spaces are not allowed as part of a name.
•Names can contain uppercase and lowercase letters, and Excel does not distinguish between them. For example, North and NORTH are treated as the same name.
•Names cannot be the same as a cell reference, such as A$35 or R2D2.
•You cannot use C,c,R or r as a defined name -- they are used as selection shortcuts.

Thanks.