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

 

[DanteAmor]

Try this:

=INDEX(H20:H28,SUMPRODUCT((I20:M28=H30)*(ROW(I20:M28)))-ROW($H$20)+1)

 

[RapchikM]

Thank you Dante Amor Sir,

=INDEX(H20:H28,SUMPRODUCT((I20:M28=H30)*(ROW(I20:M28)))-ROW($H$20)+1)

To understand the Formula provided by you

Why have you taken Sum Product and Row and How Will SUMPRODCUT help

Could you please breakup the formula and explain ?


As per your Formula If i am not mistaken you have taken Index to return the Value of Row only and not column as i see only 1 comma
using index Formula

and Why does =VLOOKUP(......) and =INDEX(.......,MATCH(....... dont take any left Columns No of defined/Refered Column

Thank
RapchikM

 

[Peter_SSs]

If your sample data is representative, perhaps you could also try this?

=--CONCAT(IF(I20:M28=H30,H20:H28,""))

 

[RapchikM]

Peter_SSs Sir,

Indeed your formula is short and sweet to incorporate

=--CONCAT(IF(I20:M28=H30,H20:H28,""))

may be data is not representative in #1 of this thread. Inconvenience regretted

Sir why have you added =-- Before CONCATE

If you don't mind can you Explain your formula Although =CONCAT is to GET different Cell values in to one cell with Delimeter (Space , Comma Semi colon etc)

Thanks
RapchikM

 

[Peter_SSs]

why have you added =-- Before CONCATE

In your sample the values being looked up in column I are numbers but CONCAT returns text. I added the -- to convert the text number back to an actual number

can you Explain your formula

The formula looks at every cell in I20:M28 and if the value in the cell is what is in H30 it takes the value from column I of the corresponding row otherwise it returns the null string "". CONCAT then joins all those values together.

=CONCAT is to GET different Cell values in to one cell with Delimeter (Space , Comma Semi colon etc)

No, CONCAT has no delimiter. It joins all the values together with nothing between.

 

[RapchikM]

Peter_SSs

Indeed very nice explanation by you Sir. Thank you very much

Just one more thing

In your sample the values being looked up in column I are numbers but CONCAT returns text. I added the -- to convert the text number back to an actual number

For Future do i have to use =-- to convert text number back to an actual number apart from CONCAT or this is used for CONCAT Case only

Regards
RapchikM

 

[Peter_SSs]

For Future do i have to use =-- to convert text number back to an actual number apart from CONCAT or this is used for CONCAT Case only

It is not for CONCAT only and there are other ways to convert a text number to an actual number. Also, sometimes there is no need to convert text numbers to actual numbers. It depends on the individual circumstances of what data you have and exactly what you are trying to do with it.

 

[RapchikM]

Peter_SSs

It is not for CONCAT only and there are other ways to convert a text number to an actual number. Also, sometimes there is no need to convert text numbers to actual numbers. It depends on the individual circumstances of what data you have and exactly what you are trying to do with it.

Thank you sir for the information.

Sir, you being MVP Could you please reply on question that I asked to Dante Amor post #3 of this thread else will await Dante Amor's Sir, Reply

Reagrds
RapchikM

 

[DanteAmor]

Sorry for the delay, I'm most likely on the other side of the world and I'm sleeping.

To understand the Formula provided by you
Why have you taken Sum Product and Row and How Will SUMPRODCUT help
Could you please breakup the formula and explain ?
As per your Formula If i am not mistaken you have taken Index to return the Value of Row only and not column as i see only 1 comma
using index Formula

To understand the sumproduct function a little more, I recommend you review the following:

SUMPRODUCT function - Microsoft Support

How to use the SUMPRODUCT function in Excel, one of Excel’s math and trig functions, using multiplication, addition, subtraction, and/or division to return the sum of the products of corresponding ranges or arrays.

support.microsoft.com

Especially this part:

=INDEX(H20:H28,SUMPRODUCT((I20:M28=H30)*(ROW(I20:M28)))-ROW($H$20)+1)


In this case, what SUMPRODUCT does is compare each cell in the range I20:M28 against cell H30, from which value is true it will obtain its row number. Since the index begins in row 20, then the initial index is row 1, that is why row 20 is subtracted from the found row, which would give us 0 (zero) and that is why a 1 is added.

The INDEX function, in this case, only needs the row number, which is why the column number is omitted.

I hope this helps you.