Index function with text instead of numbers?

[Stevee_p]

Hi, I hope someone can help with this issue im having, its driving me nuts!

I am using the INDEX function to find a specific value within a table, but the column and rows I need to reference have names, rather than numerical values eg the tables columns are titled 55, 63, 75, 90 etc etc (not ascending order, but random other than that) and the rows are named
LPsdr11
LPsdr17.6
MPsdr11
MPsdr17.6 etc etc.

When I use the index function, it does not work, yet if I replace each column / row name with a number (1,2,3,4,5 etc) then the formula works.

Can anyone help? Perhaps I need a different formula / function?

Thanks in advance

 

[kunal_shrivastava]

Hi Stevee_p,
Can you please share sample data and describe more on how you are using INDEX function?

Regards,
Kunal

 

[Stevee_p]

Hi Stevee_p,
Can you please share sample data and describe more on how you are using INDEX function?

Regards,
Kunal

Hi Kunal,

sample data is all numbers:

55 63 75 90 110 125 140 160
LPsdr11 2.88 3.78 5.40 7.74 11.52 14.76 18.54 24.30
LPsdr17.6 3.24 4.32 6.12 9.00 13.32 17.28 21.60 28.26
MPsdr11 2.52 3.24 4.68 6.66 10.08 12.96 16.38 21.24
MPsdr17.6 2.88 3.96 5.58 7.92 11.88 15.30 19.08 25.05
MPsdr21 3.06 3.96 5.76 8.28 12.24 15.84 19.98 26.10
MPsdr26 3.24 4.14 5.94 8.64 12.78 16.56 20.70 27.18
IP6/4sdr11 na 3.24 4.50 6.48 9.54 12.42 15.66 20.34
IP7/5.5sdr11 3.24 4.14 5.94 8.64 12.78 16.56 20.70 27.18
IP7/7sdr11 na 6.66 9.36 13.50 19.98 25.92 32.40 42.48

Apologies for the layout of the above, I cannot seem to add a table.

The column names can be seen as 55, 63, 75, 90 etc
and the row names as LPsdr 11 etc.

The data is all the figures with decimal points.

Does this help?

 

[kunal_shrivastava]

Hi Stevee_p,

Can we use a Uer defined function for resolving this?


Regards,
Kunal

 

[snoopyhr]

Would this work for you ?

Excel Workbook
	A	B	C	D	E	F	G	H	I
1		55	63	75	90	110	125	140	160
2	LPsdr11	2.88	3.78	5.4	7.74	11.52	14.76	18.54	24.3
3	LPsdr17.6	3.24	4.32	6.12	9	13.32	17.28	21.6	28.26
4	MPsdr11	2.52	3.24	4.68	6.66	10.08	12.96	16.38	21.24
5	MPsdr17.6	2.88	3.96	5.58	7.92	11.88	15.3	19.08	25.05
6	MPsdr21	3.06	3.96	5.76	8.28	12.24	15.84	19.98	26.1
7	MPsdr26	3.24	4.14	5.94	8.64	12.78	16.56	20.7	27.18
8	IP6/4sdr11	na	3.24	4.5	6.48	9.54	12.42	15.66	20.34
9	IP7/5.5sdr11	3.24	4.14	5.94	8.64	12.78	16.56	20.7	27.18
10	IP7/7sdr11	na	6.66	9.36	13.5	19.98	25.92	32.4	42.48
11
12
13	Drop down list for column heading				90
14	Drop down list for row heading				MPsdr11
15
16	Result for :
17	Row	Column	Result
18	MPsdr11	90	6.66
Sheet7

#VALUE!

 

[kunal_shrivastava]

Hi Stevee_p,

For creating a UDF you need to press ALT + F11 in you sheet then double click on module1 and paste the below code

Function INDEX1(r As Range, col As String, row As String)
Dim column_var
Dim row_var
For Each I In r
    If col = I Then
        column_var = I.Column
    End If
Next
For Each I In r
    If row = I Then
        row_var = I.row
    End If
Next
INDEX1 = Sheet1.Cells(row_var, column_var)
End Function

Next in your sheet use the below function as below:

=index1(A1:I10,"55","LPsdr11")

The function accepts range as first parameter , column and row as second and third param respectively..

 

[Stevee_p]

Snoopyhr,

That worked perfectly, thank you very much! What's more, I understand the logic behind it. thanks again.

Steve

 

[snoopyhr]

Welcome glad it worked