Failed in order to derive Row number or Value in cell of particular Row Using Vlookup and Index... Match

[RapchikM]

Hello

I have the Following Data

Cols

Rows	H	I	J	K	L	M
20	1	A	I	Q	W	Z
21	2	B	J	R
22	3	C	K	S	X
23	4	D	L	T
24	5	E	M	U	Y
25	6	F	N	V
26	7	G	O
27	8	H	P
28	9	-

Above Rows are from 20 to 28 and Cols from H to M

If in Cell H30 is Typed A or I or Q or W or Z Then Cell I30 = 1
If in Cell H30 is Typed D or L or T Then Cell I30 = 4
If in Cell H30 is Typed G or O Then Cell I30 = 7

How to get the Value in Cell I30 when a value in range from H20 or I20 to M28 is Matched with cell H30

Miserably failed with

Cell I30
=VLOOKUP($H$30,$H$20:$M$28,COLUMN(H20),FALSE)

Cell I30
=INDEX($H$20:$M$28,MATCH($H30,$I$20:$M$28),MATCH($H30,$H$20:$M$28))

Any other Formula where the above result is appropriately derived

Thanks
RapchikM

 

[RapchikM]

Thank you Dante Amor Sir,

For your indeed valuable Explanation and providing the reference link

Thank you very much Dante Amor Sir and Peter_SSs Sir,

Unfortunately cannot Tick on your Both Solutions. (MrExcel.com should allow more tick marks for the solutions of the thread)

Both are indeed two great ways to achieve the result

Thanks
RapchikM