Re – Excel INDEX worksheet function

[TKT-Tang]

Re – Excel INDEX worksheet function

For the ease of simulating the query, please proceed as follows :-

1. Enter the following text strings into the Range A1:A5 of an Excel
spreadsheet respectively :-
2. A1 = "A. " ; namely, A[dot]Blank ;
it's typically given to facilitate right-alignment in the respective
cells.
3. A2 = "AB. "
4. A3 = "ABC. "

5. Enter the following numbers into the Range B13 respectively :-
6. B1=1, C1=2, D1=3 ;
7. B2=4, C2=5, D2=6 ;
8. B1=7, C1=8, D1=9 ;

9. Enter the following numbers into the Range B5:J5 respectively :-
10. B5=1, C5=2, D5=3, E5=4, F5=5, G5=6, H5=7, I5=8, J5=9.

11. Enter into the cell B6 An array formula,
{=LEFT(
INDEX($A$1:$A$3,MAX(IF($B$1:$D$3=B5,ROW($B$1:$D$3),"na"))),
LEN($A$1:$A$3)-2)}

12. Explanation : IF($B$1:$D$3=B5 ; it attempts to locate B5 within
the Range $B$1:$D$3 by evaluating the Row Number thereof namely,
ROW($B$1:$D$3).

13. The Row Number is used as an Index in order to select one from the
contents of $A$1:$A$3.

14. The contents obtained from $A$1:$A$3 are to be modified by the
LEFT function by truncating 2 trailing characters (namely, the [dot]
and the Space).

15. Fill right beginning at cell B6 in the Range B6:J6 ; the given
range shows a series of 9 letters namely, all A's.

16. However, the A's are not the expected answers thereof.

17. Expected ones to be displayed in the given Range are shown as
follows :-

18. B6=A, C6=A, D6=A,
19. E6=AB, F6=AB, G6=AB,
20. H6=ABC, I6=ABC, J6=ABC.

21. Please advise the correction required of the array formulae
entered in the Range B6:J6.

22. Regards.