Index match vlookup

[nonameghost]

excel 2003, have a single sheet data range D1:AY65000. Alternate columns from D to AX has same type of value and from E to AY other type.wish to vlookup A1 value from columns D, F, H......... and get value from columns E, G, I............... in B1. Since data is large, need some dynamic type formula. Anyone please help

 

[CharlesChuckieCharles]

Does the value in A1 appear more than once in the data set? or even multiple times in a given column?

How do you want the values represented in B1

 

[jim may]

Wild Guess.. Copy Cell B1 formula Down

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M

<COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5"><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]
[TD="align: center"]DDD[/TD]
[TD="bgcolor: #ffd966, align: center"]K1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #ffff00, align: center"]372[/TD]
[TD="bgcolor: #ffff00, align: center"]AAA[/TD]
[TD="bgcolor: #ffff00, align: center"]181[/TD]
[TD="bgcolor: #ffff00, align: center"]BBB[/TD]
[TD="bgcolor: #ffff00, align: center"]197[/TD]
[TD="bgcolor: #ffff00, align: center"]CCC[/TD]
[TD="bgcolor: #ffff00, align: center"]314[/TD]
[TD="bgcolor: #ffff00, align: center"]DDD[/TD]
[TD="bgcolor: #ffff00, align: center"]255[/TD]
[TD="bgcolor: #ffff00, align: center"]EEE[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]165[/TD]
[TD="bgcolor: #ffd966, align: center"]L2[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #ffff00, align: center"]350[/TD]
[TD="bgcolor: #ffff00, align: center"]AAA[/TD]
[TD="bgcolor: #ffff00, align: center"]467[/TD]
[TD="bgcolor: #ffff00, align: center"]BBB[/TD]
[TD="bgcolor: #ffff00, align: center"]451[/TD]
[TD="bgcolor: #ffff00, align: center"]CCC[/TD]
[TD="bgcolor: #ffff00, align: center"]223[/TD]
[TD="bgcolor: #ffff00, align: center"]DDD[/TD]
[TD="bgcolor: #ffff00, align: center"]165[/TD]
[TD="bgcolor: #ffff00, align: center"]EEE[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]255[/TD]
[TD="bgcolor: #ffd966, align: center"]H3[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #ffff00, align: center"]209[/TD]
[TD="bgcolor: #ffff00, align: center"]AAA[/TD]
[TD="bgcolor: #ffff00, align: center"]292[/TD]
[TD="bgcolor: #ffff00, align: center"]BBB[/TD]
[TD="bgcolor: #ffff00, align: center"]255[/TD]
[TD="bgcolor: #ffff00, align: center"]CCC[/TD]
[TD="bgcolor: #ffff00, align: center"]231[/TD]
[TD="bgcolor: #ffff00, align: center"]DDD[/TD]
[TD="bgcolor: #ffff00, align: center"]209[/TD]
[TD="bgcolor: #ffff00, align: center"]EEE[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]331[/TD]
[TD="bgcolor: #ffd966, align: center"]D4[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #ffff00, align: center"]331[/TD]
[TD="bgcolor: #ffff00, align: center"]AAA[/TD]
[TD="bgcolor: #ffff00, align: center"]169[/TD]
[TD="bgcolor: #ffff00, align: center"]BBB[/TD]
[TD="bgcolor: #ffff00, align: center"]108[/TD]
[TD="bgcolor: #ffff00, align: center"]CCC[/TD]
[TD="bgcolor: #ffff00, align: center"]488[/TD]
[TD="bgcolor: #ffff00, align: center"]DDD[/TD]
[TD="bgcolor: #ffff00, align: center"]179[/TD]
[TD="bgcolor: #ffff00, align: center"]EEE[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]BBB[/TD]
[TD="bgcolor: #ffd966, align: center"]G5[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #ffff00, align: center"]164[/TD]
[TD="bgcolor: #ffff00, align: center"]AAA[/TD]
[TD="bgcolor: #ffff00, align: center"]470[/TD]
[TD="bgcolor: #ffff00, align: center"]BBB[/TD]
[TD="bgcolor: #ffff00, align: center"]386[/TD]
[TD="bgcolor: #ffff00, align: center"]CCC[/TD]
[TD="bgcolor: #ffff00, align: center"]216[/TD]
[TD="bgcolor: #ffff00, align: center"]DDD[/TD]
[TD="bgcolor: #ffff00, align: center"]413[/TD]
[TD="bgcolor: #ffff00, align: center"]EEE[/TD]

</TBODY>

Sheet1​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: #dae7f5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: #dae7f5"]B1[/TH]
[TD="align: left"]=ADDRESS(ROW(),COLUMN(INDEX(D1:M1,MATCH(A1,D1:M1,0))),4,,)[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[nonameghost]

Thanks for the response. B1 should display the value as in vlookup not the address of the cell. Plz keep in mind, data is very large

=IF(A1="","",INDIRECT(ADDRESS(SUMPRODUCT(($D$1:$AY$65000=A1)*ROW($D$1:$AY$65000)),SUMPRODUCT(($D$1:$AY$65000=A1)*COLUMN($D$1:$AY$65000))+1)))

works fine but response is at crawling speed

thanks