VLOOKUP LookUp Value 255 Character limit

[DocAElstein]

Re: VLOOKUP LookUp Value 255 Character limit

Hi,
. Not a big problem. (I have a few (complicated) ideas already). But Just on the off-chance someone knows a simple solution….
. Partly as a learning process and partly in preparation for a project involving big complicated Files and data tables, I am comparing different methods to look up and sort large files.

. Amongst other things I am looking at VLOOKUP. I hit a problem. After a bit of googling and trial and error I realized it was because of a limit in the LookUp Value in the LOOKUP Function of 255 characters.

. So to give an example. In the following spreadsheet you can see I have an error for the pink entry as the look Up value exceeds 255 characters.

Book1
	A	B	C
1	Product
2	Name
3	*** SOYAsojasource 77/,1/12/6,9/3,7/,6/8,3/ soysoacjap 39/0/3,5/6/ / / /spanish vineger 26/0/,023/,53//// balsamico 87/0/1/16/13/0/,05/		1
4	ErroskiWine		2
5	SENF english 170/6,7/6/21//// grill & steak 118/6,3/5,8/8,1//// sweetpickle130/,2/,8/31////		3
6	SCHMELZKSE/WWSPREAD philadelphia 179/15/7,6/4/,1/,5/,4/ kruter 152/11/7,8/4,8/4,6/,7/,5/ leicht110/4,7/11,5/5/5/,4/,4/ kruter 186/15/9/3,7/ / /brlauch 296/28/5/5/3/1/,7/ bresco 230/19,5/8,2/5,4////frischksekruter 186/15/9/3,7/ / / /brunchkraut/gurk 215/20,5/4,2/3,5/3/,1/,45/cleop 220/20/4,5/6/5/,1/,35/ bresco 230/19,5/8,2/5,4////		####
7
8
9	Look Up
10	Table
11	*** SOYAsojasource 77/,1/12/6,9/3,7/,6/8,3/ soysoacjap 39/0/3,5/6/ / / /spanish vineger 26/0/,023/,53//// balsamico 87/0/1/16/13/0/,05/		1
12	SENF english 170/6,7/6/21//// grill & steak 118/6,3/5,8/8,1//// sweetpickle130/,2/,8/31////		3
13	ErroskiWine		2
14	SCHMELZKSE/WWSPREAD philadelphia 179/15/7,6/4/,1/,5/,4/ kruter 152/11/7,8/4,8/4,6/,7/,5/ leicht110/4,7/11,5/5/5/,4/,4/ kruter 186/15/9/3,7/ / /brlauch 296/28/5/5/3/1/,7/ bresco 230/19,5/8,2/5,4////frischksekruter 186/15/9/3,7/ / / /brunchkraut/gurk 215/20,5/4,2/3,5/3/,1/,45/cleop 220/20/4,5/6/5/,1/,35/ bresco 230/19,5/8,2/5,4////		4
Tabelle1

Cell Formulas
Range	Formula
C3	=VLOOKUP(A3,$A$11:$C$14,3,FALSE)
C4	=VLOOKUP(A4,$A$11:$C$14,3,FALSE)
C5	=VLOOKUP(A5,$A$11:$C$14,3,FALSE)
C6	=VLOOKUP(A6,$A$11:$C$14,3,FALSE)

. I can think of many complicated ways to overcome this problem such as using temporary columns with truncated values in comparing and looking up etc., but as speed may be important in my final large files I was hoping for a simple “one liner” solution.

. I was thinking along the lines something like of modifying this type of code….

=VLOOKUP(A6,$A$11:$C$14,3,FALSE)

to something like this

=VLOOKUP([COLOR=#FF0000]LEFT(A6,10)[/COLOR],$A$11:$C$15,[COLOR=#0000CD]LEFT(3,10)[/COLOR],FALSE

. By experimenting I have found that VBA is happy with the Red highlighted modification but does not like the blue highlighted modification.

. Can anyone suggest a one-liner code syntax modification that works?

Thanks
Alan

 

[DocAElstein]

<o>
Hi Dominic.<o></o>

<o> </o>

..

………the formulas I offered, have a look at the sample workbook to see if it helps...

....<o></o>

……

<o></o>

<o> </o>

. Many thanks. That helped greatly. It came upin my German Excel with perfect formula translations which worked perfectlygiving exactly the results I was looking for. Perfect Great.<o></o>

. The File also was a great help in quickeningfinding where I had been going wrong. There were some tricky syntax and FunctionTranslations as well as some annoying Editor characteristics that were makingmy manual translations somewhat difficult. But if I am honest about it, themain ‘spanner in the works’ was one mistake in a few places by me….In factRoryA was spot on and stupidly I put his observation down to a characteristicof the MrExcel HTML Maker and Editor, which IN THIS CASE was not the problem…..

You should be using TRUE and not 'TRUE' in quotes in theformula.

.. Indeed somewhere along the line I had forgotten totranslate a TRUE from you to a German WAHR.(So correctly the MrExcel HTML Maker saw this as a string “TRUE” rather thantranslating it from the Boolean thing WAHR in German to the Boolean thing TRUE in English – which as RoryA pointed outwas an indication of my mistake!!)<o></o>

. I apologies that because of my incompetence youneeded to send the File to me. But thatwas a very helpful time saver in tracking down my Error. So it was very muchappreciated.<o></o>

. I am still struggling to pull that formulaapart to find out exactly how it works, but that is due to my total lack ofexperience with INDEX etc. especially in conjunction with Boolean stuff. But Iwill keep at it when I can***. In themeantime I have working formula and again, many thanks for that..<o></o>

<o> </o>

Alan Elston<o></o>

<o> </o>

** I can put into wordswot I think is going on… “.. INDEX($A$11:$A$14=A_,0) gives a TRUE for a namematch, and presumably makes note (somewhere?) of where the name match was found. (Possiblyin the form of an array??) >> Then MATCH(TRUE,INDEX($A$11:$A$14=A_,0),0) is able to access where this match was found, >> Then finally the last bit INDEX($C$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A_,0),0))Picks out just the value where the match occured.…”. <o></o>

. But I can not quite yet then explain thefollowing where I have broken the formula down into it’s different parts.?!? <o></o>

<o> </o>

<o> </o>

<b></b><tablecellpadding="2.5px" rules="all"style=";background-color: #FFFFFF;border: 1px solid;border-collapse:collapse; border-color: #A6AAB6"><colgroup><colwidth="25px" style="background-color: #E0E0F0" /><col/><col /><col /><col /></colgroup><thead><trstyle=" background-color: #E0E0F0;text-align: center;color:#161120"><th></th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr><td style="color: #161120;text-align:center;">1</td><td style="text-align:center;;">Domenic 1 & 2</td><td style=";">  = INDEX($A$11:$A$14=A_;0)</td><tdstyle=";">  =VERGLEICH(WAHR;INDEX($A$11:$A$14=A_;0);0))</td><tdstyle=";">   =INDEX($C$11:$C$14;VERGLEICH(WAHR;INDEX($A$11:$A$14=A_;0);0))</td></tr><tr><td style="color: #161120;text-align:center;">2</td><td style=";">CorrectedSyntax</td><td style=";"> INDEX($A$11:$A$14=A_,0</td><td style=";"> MATCH(TRUE,INDEX($A$11:$A$14=A_,0),0</td><tdstyle=";"> INDEX($C$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A_,0),0)) </td></tr><tr><td style="color: #161120;text-align:center;">3</td><td style="text-align:center;;">1</td><td style="text-align:center;;">WAHR</td><td style="text-align:center;;">1</td><td style="text-align:center;;">1</td></tr><tr ><td style="color:#161120;text-align: center;">4</td><td style="text-align:center;;">2</td><td style="text-align:center;;">FALSCH</td><td style="text-align:center;;">3</td><td style="text-align:center;;">2</td></tr><tr ><td style="color:#161120;text-align: center;">5</td><td style="text-align:center;;">3</td><td style="text-align:center;;">FALSCH</td><td style="text-align:center;;">2</td><td style="text-align:center;;">3</td></tr><tr ><td style="color:#161120;text-align: center;">6</td><td style="text-align:center;;">4</td><td style="text-align:center;;">FALSCH</td><td style="text-align:center;;">4</td><td style="text-align:center;;">4</td></tr></tbody></table><pstyle="width:4,8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em0.5em;border: 1px solid #A6AAB6;border-top:none;text-align:center;background-color: #E0E0F0;color:#161120">Tabelle1</p><br /><br /><tablewidth="85%" cellpadding="2.5px" rules="all"style=";border: 2px solid black;border-collapse:collapse;padding:0.4em;background-color: #FFFFFF" ><tr><tdstyle="padding:6px" ><b>WorksheetFormulas</b><table cellpadding="2.5px"width="100%" rules="all" style="border: 1pxsolid;text-align:center;background-color: #FFFFFF;border-collapse: collapse;border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;color: #161120"><thwidth="10px">Cell</th><thstyle="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><thwidth="10px" style=" background-color: #E0E0F0;color:#161120">I3</th><tdstyle="text-align:left">=INDEX(<font color="Blue">$C$11:$C$14,MATCH(<fontcolor="Red">TRUE,INDEX(<fontcolor="Green">$A$11:$A$14=A3,0</font>),0</font>)</font>)</td></tr><tr><thwidth="10px" style=" background-color: #E0E0F0;color:#161120">J3</th><tdstyle="text-align:left">=INDEX(<fontcolor="Blue">$A$11:$A$14=A3,0</font>)</td></tr><tr><thwidth="10px" style=" background-color: #E0E0F0;color:#161120">K3</th><tdstyle="text-align:left">=MATCH(<fontcolor="Blue">TRUE,INDEX(<fontcolor="Red">$A$11:$A$14=A3,0</font>),0</font>)</td></tr><tr><thwidth="10px" style=" background-color: #E0E0F0;color:#161120">L3</th><tdstyle="text-align:left">=INDEX(<fontcolor="Blue">$C$11:$C$14,MATCH(<fontcolor="Red">TRUE,INDEX(<font color="Green">$A$11:$A$14=A3,0</font>),0</font>)</font>)</td></tr><tr><thwidth="10px" style=" background-color: #E0E0F0;color:#161120">I4</th><tdstyle="text-align:left">=INDEX(<fontcolor="Blue">$C$11:$C$14,MATCH(<fontcolor="Red">TRUE,INDEX(<font color="Green">$A$11:$A$14=A4,0</font>),0</font>)</font>)</td></tr><tr><thwidth="10px" style=" background-color: #E0E0F0;color:#161120">J4</th><tdstyle="text-align:left">=INDEX(<fontcolor="Blue">$A$11:$A$14=A4,0</font>)</td></tr><tr><thwidth="10px" style=" background-color: #E0E0F0;color:#161120">K4</th><tdstyle="text-align:left">=MATCH(<fontcolor="Blue">TRUE,INDEX(<fontcolor="Red">$A$11:$A$14=A4,0</font>),0</font>)</td></tr><tr><thwidth="10px" style=" background-color: #E0E0F0;color:#161120">L4</th><tdstyle="text-align:left">=INDEX(<fontcolor="Blue">$C$11:$C$14,MATCH(<font color="Red">TRUE,INDEX(<fontcolor="Green">$A$11:$A$14=A4,0</font>),0</font>)</font>)</td></tr><tr><thwidth="10px" style=" background-color: #E0E0F0;color:#161120">I5</th><td style="text-align:left">=INDEX(<fontcolor="Blue">$C$11:$C$14,MATCH(<fontcolor="Red">TRUE,INDEX(<fontcolor="Green">$A$11:$A$14=A5,0</font>),0</font>)</font>)</td></tr><tr><thwidth="10px" style=" background-color: #E0E0F0;color:#161120">J5</th><td style="text-align:left">=INDEX(<fontcolor="Blue">$A$11:$A$14=A5,0</font>)</td></tr><tr><thwidth="10px" style=" background-color: #E0E0F0;color:#161120">K5</th><tdstyle="text-align:left">=MATCH(<fontcolor="Blue">TRUE,INDEX(<fontcolor="Red">$A$11:$A$14=A5,0</font>),0</font>)</td></tr><tr><thwidth="10px" style=" background-color: #E0E0F0;color: #161120">L5</th><tdstyle="text-align:left">=INDEX(<fontcolor="Blue">$C$11:$C$14,MATCH(<fontcolor="Red">TRUE,INDEX(<fontcolor="Green">$A$11:$A$14=A5,0</font>),0</font>)</font>)</td></tr><tr><thwidth="10px" style=" background-color: #E0E0F0;color: #161120">I6</th><tdstyle="text-align:left">=INDEX(<fontcolor="Blue">$C$11:$C$14,MATCH(<fontcolor="Red">TRUE,INDEX(<fontcolor="Green">$A$11:$A$14=A6,0</font>),0</font>)</font>)</td></tr><tr><thwidth="10px" style=" background-color: #E0E0F0;color:#161120">J6</th><tdstyle="text-align:left">=INDEX(<fontcolor="Blue">$A$11:$A$14=A6,0</font>)</td></tr><tr><thwidth="10px" style=" background-color: #E0E0F0;color:#161120">K6</th><tdstyle="text-align:left">=MATCH(<fontcolor="Blue">TRUE,INDEX(<font color="Red">$A$11:$A$14=A6,0</font>),0</font>)</td></tr><tr><thwidth="10px" style=" background-color: #E0E0F0;color:#161120">L6</th><tdstyle="text-align:left">=INDEX(<font color="Blue">$C$11:$C$14,MATCH(<fontcolor="Red">TRUE,INDEX(<fontcolor="Green">$A$11:$A$14=A6,0</font>),0</font>)</font>)</td></tr></tbody></table></td></tr></table><br/><o></o>

<o> </o>

. I expect the explanation is coming back to some very interesting points anddiscussions discussed in <o></o>

http://www.mrexcel.com/forum/excel-questions/806702-visual-basic-applications-evaluate-range-vlookup.html?#post3944034<o></o>

<o> </o>

I will keep at it. (whenI can!)<o></o>
</o>

 

[DocAElstein]

That Table Again!!....

Book1
	I	J	K	L
1	Domenic 1 & 2	= INDEX($A$11:$A$14=A_;0)	=VERGLEICH(WAHR;INDEX($A$11:$A$14=A_;0);0))	=INDEX($C$11:$C$14;VERGLEICH(WAHR;INDEX($A$11:$A$14=A_;0);0))
2	Corrected Syntax	INDEX($A$11:$A$14=A_,0	MATCH(TRUE,INDEX($A$11:$A$14=A_,0),0	INDEX($C$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A_,0),0))
3	1	WAHR	1	1
4	2	FALSCH	3	2
5	3	FALSCH	2	3
6	4	FALSCH	4	4
Tabelle1

Cell Formulas
Range	Formula
I3	=INDEX($C$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A3,0),0))
I4	=INDEX($C$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A4,0),0))
I5	=INDEX($C$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A5,0),0))
I6	=INDEX($C$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A6,0),0))
J3	=INDEX($A$11:$A$14=A3,0)
J4	=INDEX($A$11:$A$14=A4,0)
J5	=INDEX($A$11:$A$14=A5,0)
J6	=INDEX($A$11:$A$14=A6,0)
K3	=MATCH(TRUE,INDEX($A$11:$A$14=A3,0),0)
K4	=MATCH(TRUE,INDEX($A$11:$A$14=A4,0),0)
K5	=MATCH(TRUE,INDEX($A$11:$A$14=A5,0),0)
K6	=MATCH(TRUE,INDEX($A$11:$A$14=A6,0),0)
L3	=INDEX($C$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A3,0),0))
L4	=INDEX($C$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A4,0),0))
L5	=INDEX($C$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A5,0),0))
L6	=INDEX($C$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A6,0),0))

 

[DocAElstein]

RoryA

..

You should be using TRUE and not 'TRUE' in quotes in the formula.

..

. Spot on (again). Sorry I was slow (again) at getting the point.
Alan.

…..But I will have to watch out for that semi colon with closed bracket thing - Puts a spanner in the works when working with German stuff. (Can disable it of course… shame… I like that particular smiley !

 

[DocAElstein]

Hi<o></o>
. I may be taking the thread over into adiscussion of the use of INDEX with MATCH as an alternative to VLOOKUP. But itis helping me to get clear on a few points that may help me in some of my ownrelated threads on looking things up in particular with Evaluate “One linercodes”. Wot I write here explains the basic Formulas here I think a bit furtherthan in many explanations I googled. ( Although this one is not bad http://www.mrexcel.com/articles/excel-vlookup-index-match.php )<o></o>
. So this reply may also be of interest topeople hitting on this thread in the future.<o></o>
. Any comments or corrections to my explanations would, of course, be verywelcome.<o></o>
<o> </o>
. Coming back to the original (Not Array )Formula offered by Dominic to me (I will leave out again for now the Array formula as that isstill a bit out of my competence area)..<o></o>
<o> </o>
. It was an alternative to the classic VLOOKUPFormula<o></o>
<o> </o>
=VLOOKUP( LookUpCriteriaorValueorcell, LookUpValuesTableorArrayorDataRange , ColumnForTheReturnedItem , 0 )<o></o>
(with the last entry 0or 1 or True or false the usualexactness of match thing)<o></o>
<o> </o>
. the offered alternative from Dominic combinesthe INDEX and MATCH function to produce the same results.<o></o>
<o> </o>
… My explanation of howthese two combined work to give the alternative to VLOOKUP:<o></o>
. The simplest form of INDEX is not much morethan giving the value or item out of agrid or table of values or items, based on the row and column number. (Give itan area and x y coordinates and it tells you wot is in the relevant cell)<o></o>
<o> </o>
=INDEX ( GridorTableorArrayorDataRange, RowNumber, ColumnNumber)<o></o>
<o> </o>
. The simplest form ofthe MATCH Function is similar to VLOOKUP but returns a co-ordinate of where amatch is found rather than returning the found value or Item<o></o>
<o> </o>
. = MATCH (LookUpCriteriaorValueorcell, LookUpValuesColumnor1DimensionaArray, 0)<o></o>
(with the last entry 0or 1 or True or false the usualexactness of match thing)<o></o>
<o> </o>
So the idea is that yousimply replace the RowNumber or ColumnNumber in the INDEX Formula with an appropriate MATCHFormula.<o></o>
<o> </o>
<o> </o>
. Going back now to my original example.<o></o>
. =VLOOKUP(A3,$A$11:$C$14,3,FALSE) This Formula can befollowing my discussions above converted to to<o></o>
=INDEX($A$11:$C$14,MATCH(A3,$A$11:$A$14,0),3)<o></o>
This can be written as<o></o>
=INDEX($C$11:$C$14,MATCH(A3,$A$11:$A$14,0),1)<o></o>
or<o></o>
=INDEX($C$11:$C$14,MATCH(A3,$A$11:$A$14,0)) Here the default taken as column 1<o></o>
<o> </o>
. <o></o>
. The following arealong the lines of Dominic’s solution<o></o>
<o> </o>
=INDEX($A$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A3,0),0),3)<o></o>
This can be written as<o></o>
=INDEX($C$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A3,0),0),1)<o></o>
or<o></o>
=INDEX($C$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A3,0),0))<o></o>
. This final form is that given by Dominic.<o></o>
<o> </o>
. The key to what is going on here is I thinkcomparing the results from these followinglines both in the formula forms andthe given results when highlighting the formula in the Formula bar and pressingF9<o></o>
<o> </o>
= INDEX($A$11:$A$14=A3,0) >>> TRUE >>F9 in Formula bar>> {TRUE; FALSE; FALSE; FALSE }<o></o>
= INDEX($A$11:$A$14=A4,0) >>> False >>F9 in Formula bar>> {FALSE; FALSE; TRUE; FALSE }<o></o>
= INDEX($A$11:$A$14=A5,0) >>> False >>F9 in Formula bar>> {FALSE; TRUE;FALSE; FALSE }<o></o>
= INDEX($A$11:$A$14=A6,0) >>> False >>F9 in Formula bar>> {FALSE; FALSE;FALSE; TRUE }<o></o>
<o> </o>
. I am not sure what the advantages are ofthe Boolean version, but I expect itcould help me in my further attempts to understand the “one liner” version ofmy VLOOKUP formula given to me by RoryA: <o></o>
<o> </o>
=TRANSPOSE(INDEX(VLOOKUP(T(IF(1,TRANSPOSE(A3))),$A$11:$C$14,3,0),))<o></o>
<o> </o>
a version of which was able to be incorporated in a one linerVBA LOOKUP Evaluate Range Code:<o></o>
<o> </o>

[color=darkblue]Sub[/color]Evaluate_VLOOKUPMEGARoryA()<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Dim[/color] rngName               [color=darkblue]As[/color] Range<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Dim[/color] rngEE                 [color=darkblue]As[/color]Range<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    <o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Set[/color] rngName =ThisWorkbook.Worksheets("Sheet1").Range("A3:A6")<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Set[/color] rngEE =ThisWorkbook.Worksheets("Sheet1").Range("E3:E6")<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    <o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    rngEE.Value =Evaluate("transpose(INDEX(VLOOKUP(T(IF(Row(),TRANSPOSE(" &rngName.Address & "))),A11:C14,3,FALSE),))")<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    <o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]End[/color][color=darkblue]Sub[/color] [color=green]'Evaluate_VLOOKUPMEGARoryA()[/color]

<o></o>
<o> </o>
<o> </o>
…Somehow the MATCHfunction is getting access to the full 4 values to use in its comparison ratherthan just the first value given in the cells A3 t0 A6. So it counts along tothe TRUE occurrence, returning the correct 1 3 2 1 sequence. If I could somehowunderstand all this, then Ideally then I could try to get an Index “One liner”along RoryA’s ideas to get away again from my 255 character limit…. But I havetried about 1000 times now to understand or convert that “Mega One-liner..”<o></o>
<o> </o>
<o> </o>
. Maybe I will reply again if I get any furtheron that one. .. <o></o>
<o> </o>
Alan.<o></o>

 

[RoryA]

Can you post a simple table of what you have (and where) and what results you want from your code?

BTW, if you are using VLOOKUP with a single value, you don't need all that TRANSPOSE(INDEX...)) stuff - that is specific to trying to return an array of results from an array of inputs.

 

[DocAElstein]

Hi RoryA
. Thanks for the reply.
. I was a bit surprised that you came back here in this thread?
. Basically I am just trying to understand your Mega “one liner” (http://www.mrexcel.com/forum/excel-...ic-applications-evaluate-range-vlookup-3.html), and therefore, for example, to apply it to a similar “Range Evaluate INDEX with MATCH” equivalent “One Liner”…

Can you post a simple table of what you have (and where) and what results you want from your code?
................

…… It might be a bit better or easier to keep with the example data and simple table that I posted in that thread.. ( http://www.mrexcel.com/forum/excel-...ic-applications-evaluate-range-vlookup-3.html )......I had replied / asked You for some further help there but the reply got lost (as we had been discussing on Friday!! - http://www.mrexcel.com/forum/about-...p-question-clarification-opinions-please.html) . I was thinking of bumping that again in a few days ( I do not like bumping too soon as I am very grateful for your help and did not want to bug you by bumping too soon as you seem very busy on the Board.)

. It may be better if I get my thoughts together now and then reply again to that thread and then if You have time we can take it further from there. So I will reply there ( http://www.mrexcel.com/forum/excel-...ic-applications-evaluate-range-vlookup-3.html ) in a few minutes

Alan

P.s.

………if you are using VLOOKUP with a single value, you don't need all that TRANSPOSE(INDEX...)) stuff - that is specific to trying to return an array of results from an array of inputs.

. I realized that. I was just Practicing and checking that I could apply your code to different situations. Also I was using various forms of these types of formulas in spreadsheets and in the test area as I was trying to pull that one liner apart to understand it!!

 

[DocAElstein]

...........then Ideally then I could try to get an Index “One liner”along RoryA’s ideas to get away again from my 255 character limit…. ...
<o> </o>
<o> </o>
. Maybe I will reply again if I get any further on that one. .. <o></o>
<o> </o>
Alan.<o></o>

Hi….<o></o>
. Just for completeness I have here the new Evaluate one liner that I have fromRoryA yesterday applied to my variations of Dominic’s formula. <o></o>
<o> </o>
=INDEX(INDEX($A$11:$C$14,N(IF(1,MATCH(A3,$A$11:$A$14,0))),3),)<o></o>
=INDEX(INDEX($C$11:$C$14,N(IF(1,MATCH(A3,$A$11:$A$14,0))),1),)<o></o>
=INDEX(INDEX($C$11:$C$14,N(IF(1,MATCH(A3,$A$11:$A$14,0)))),)<o></o>
<o> </o>
And here are the codevariations applied to the examples in this Thread<o></o>
(It puts the calculatedvalues in Column JJ)<o></o>
<o> </o>
<o> </o>

<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]Sub[/color]Evaluate_VLOOKUPandINDEXwithMATCHMEGARoryA()<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]<o:p> </o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Dim[/color] rngName               [color=darkblue]As[/color] Range<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Dim[/color] rngEE[color=darkblue]As[/color] Range, rngJJ [color=darkblue]As[/color] Range<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    <o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Set[/color] rngName =ThisWorkbook.Worksheets("Sheet1").Range("A3:A6")<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Set[/color] rngEE =ThisWorkbook.Worksheets("Sheet1").Range("E3:E6")<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=darkblue]Set[/color] rngJJ =ThisWorkbook.Worksheets("Sheet1").Range("J3:J6")<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    <o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    rngEE.Value =Evaluate("transpose(INDEX(VLOOKUP(T(IF(Row(),TRANSPOSE(" &rngName.Address & "))),A11:C14,3,FALSE),))")<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    rngJJ.Value =Evaluate("INDEX(INDEX(A11:C14,N(IF(Row(),MATCH(" & rngName.Address& ",A11:A14,0))),3),)")<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    rngJJ.Value =Evaluate("INDEX(INDEX(A11:C14,N(IF(1,MATCH(" & rngName.Address& ",A11:A14,0))),3),)")<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    rngJJ.Value =Evaluate("INDEX(INDEX(A11:C14,N(IF(1,MATCH(A3:A6,A11:A14,0))),3),)")<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=green]'    rngJJ.Value =Evaluate("INDEX(INDEX(A11:C14,N(IF(1,MATCH(TRUE,INDEX($A$11:$A$14=A3,0),0))),3),)")[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=green]'    rngJJ.Value =Evaluate("INDEX(INDEX(C11:C14,N(IF(1,MATCH(TRUE,INDEX($A$11:$A$14=A3,0),0))),1),)")[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]    [color=green]'    rngJJ.Value =Evaluate("INDEX(INDEX(C11:C14,N(IF(1,MATCH(TRUE,INDEX($A$11:$A$14=A3,0),0)))),)")[/color]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"][color=darkblue]End[/color][color=darkblue]Sub[/color] [color=green]'Evaluate_VLOOKUPandINDEXwithMATCHMEGARoryA()[/color]

<o></o>
<o> </o>
. Unfortunately the spreadsheet and codeversions of these formulas DO NOTseem to overcome the 255 character limit, as indeed I now see is also the casefor my spreadsheet version of Dominic’s formula.<o></o>
<o> </o>
. Unfortunately I was not able to getDominic’s original formulas<o></o>
<o> </o>
=INDEX($A$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A3,0),0),3)<o></o>
=INDEX($C$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A3,0),0),1)<o></o>
=INDEX($C$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A3,0),0))<o></o>
<o> </o>
to work in the code…..<o></o>
…..So I do not have asolution to overcome the 255 character limit in an Evaluate version of any ofthe formulas discussed here, as I was hoping as a final goal to achieve in thisThread<o></o>
<o> </o>
…..I will go back toRoryA in the other Thread ( http://www.mrexcel.com/forum/excel-...ic-applications-evaluate-range-vlookup-3.html ) now andask for his help again <o></o>
<o> </o>
Alan.<o></o>

 

[DocAElstein]

.......
<o> </o>
. Unfortunately the spreadsheet and codeversions of these formulas DO NOTseem to overcome the 255 character limit, as indeed I now see is also the casefor my spreadsheet version of Dominic’s formula.<o></o>
<o> </o>
. Unfortunately I was not able to getDominic’s original formulas<o></o>
<o> </o>
=INDEX($A$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A3,0),0),3)<o></o>
=INDEX($C$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A3,0),0),1)<o></o>
=INDEX($C$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A3,0),0))<o></o>
<o> </o>
to work in the code…..<o></o>
…..So I do not have asolution to overcome the 255 character limit in an Evaluate version of any ofthe formulas discussed here, as I was hoping as a final goal to achieve in thisThread<o></o>
<o> </o>
…..I will go back.....
Alan.<o></o>

Just to feedback onthis last bit…<o></o>
… It still has notproved possible to get Dominic’s codes to work in the Range Evaluate one linercode idea. <o></o>
<o> </o>
. But RoryA has somehow produced a code variationthat does overcome this problem. Forcomparison here I re wrote it in a spreadsheet versions. <o></o>
<o> </o>
. =INDEX(INDEX($C$11:$C$14,N(IF(1,MMULT(N(TRANSPOSE($A$11:$A$14)=$A$3:$A$6),ROW($A$11:$A$14)-ROW($A$10))))),)<o></o>
. =INDEX(INDEX($C$11:$C$14,N(IF(1,MMULT(N(TRANSPOSE($A$11:$A$14)=$A$3:$A$6),ROW($A$11:$A$14)-ROW($A$10)))),1),)<o></o>
. =INDEX(INDEX($A$11:$C$14,N(IF(1,MMULT(N(TRANSPOSE($A$11:$A$14)=$A$3:$A$6),ROW($A$11:$A$14)-ROW($A$10)))),3),)<o></o>
<o> </o>
. For reasons I do not yet understand I hadto “array enter” these formulas…..Method: Copy Formula to Clipboard,Selectcells, press f2, paste the formula in,<o></o>
Then enter withCtrl+Shift+Enter. If entered correctly, Excel willsurround with curly braces {}.
Note1 : Do not try and enter the {} manuallyyourself<o></o>
Note 2 : You must notselect all cells but results you get will start from the first lookup row……<o></o>
<o> </o>
The results to date forcomparison I give in the next Post/ Reply #19<o></o>
<o> </o>
. <o></o>
<o> </o>
(. Out of interest, Any other code variationswould be welcome, particularly ones that might be possible to “coerce” to workin A VBA Code Range Evaluate One liner as discussed in the other threads). <o></o>
Alan<o></o>
<o> </o>
P.s. It provedimpossible for me to copy in Dominic’s First “array enter” Formula. To overcomethat one in order to produce the sample results above I had to do a very longway around “bodge”. – I took the file Dominic kindly provided, deleted everythingexcept his First “array enter” Formulaand wrote everything else around it!?! Possibly a strange Bug in the German toEnglish Excel version? So I am very grateful again to Domenic for providingthat File!<o></o>
<o> </o>
Here is my Bodgedversion of that File.<o></o>
<o></o>
https://www.dropbox.com/s/xayhu48czscv03n/BodgeForMrExcel.xlsx?dl=0<o></o>
<o> </o>

 

[DocAElstein]

Book1
	A	B	C	D	E	F	G	H	I
1	Product	Alan	Domenic1	Domenic2		Alan Domenic	Alan Rory	Rory Alan	RoryA
2	Name	VLOOKUP	Array Enter Index Formula	Index Formula		Index Formula	Index Formula	Array Enter Index Formula	VBA VLOOKUP for VBA Evaluate Range
3	*** SOYAsojasource 77/,1/12/6,9/3,7/,6/8,3/ soysoacjap 39/0/3,5/6/ / / /spanish vineger 26/0/,023/,53//// balsamico 87/0/1/16/13/0/,05/	1	1	1		1	1	1	1
4	ErroskiWine	2	2	2		2	2	2	2
5	SENF english 170/6,7/6/21//// grill & steak 118/6,3/5,8/8,1//// sweetpickle130/,2/,8/31////	3	3	3		3	3	3	3
6	SCHMELZKSE/WWSPREAD philadelphia 179/15/7,6/4/,1/,5/,4/ kruter 152/11/7,8/4,8/4,6/,7/,5/ leicht110/4,7/11,5/5/5/,4/,4/ kruter 186/15/9/3,7/ / /brlauch 296/28/5/5/3/1/,7/ bresco 230/19,5/8,2/5,4////frischksekruter 186/15/9/3,7/ / / /brunchkraut/gurk 215/20,5/4,2/3,5/3/,1/,45/cleop 220/20/4,5/6/5/,1/,35/ bresco 230/19,5/8,2/5,4////	#WERT!	4	4		#WERT!	#WERT!	4	#WERT!
7
8
9	Look Up
10	Table
11	*** SOYAsojasource 77/,1/12/6,9/3,7/,6/8,3/ soysoacjap 39/0/3,5/6/ / / /spanish vineger 26/0/,023/,53//// balsamico 87/0/1/16/13/0/,05/		1
12	SENF english 170/6,7/6/21//// grill & steak 118/6,3/5,8/8,1//// sweetpickle130/,2/,8/31////		3
13	ErroskiWine		2
14	SCHMELZKSE/WWSPREAD philadelphia 179/15/7,6/4/,1/,5/,4/ kruter 152/11/7,8/4,8/4,6/,7/,5/ leicht110/4,7/11,5/5/5/,4/,4/ kruter 186/15/9/3,7/ / /brlauch 296/28/5/5/3/1/,7/ bresco 230/19,5/8,2/5,4////frischksekruter 186/15/9/3,7/ / / /brunchkraut/gurk 215/20,5/4,2/3,5/3/,1/,45/cleop 220/20/4,5/6/5/,1/,35/ bresco 230/19,5/8,2/5,4////		4
Sheet1

Cell Formulas
Range	Formula
B3	=VLOOKUP(A3,$A$11:$C$14,3,FALSE)
B4	=VLOOKUP(A4,$A$11:$C$14,3,FALSE)
B5	=VLOOKUP(A5,$A$11:$C$14,3,FALSE)
B6	=VLOOKUP(A6,$A$11:$C$14,3,FALSE)
D3	=INDEX($C$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A3,0),0))
D4	=INDEX($C$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A4,0),0))
D5	=INDEX($C$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A5,0),0))
D6	=INDEX($C$11:$C$14,MATCH(TRUE,INDEX($A$11:$A$14=A6,0),0))
F3	=INDEX($A$11:$C$14,MATCH(A3,$A$11:$A$14,0),3)
F4	=INDEX($A$11:$C$14,MATCH(A4,$A$11:$A$14,0),3)
F5	=INDEX($A$11:$C$14,MATCH(A5,$A$11:$A$14,0),3)
F6	=INDEX($A$11:$C$14,MATCH(A6,$A$11:$A$14,0),3)
G3	=INDEX(INDEX($A$11:$C$14,N(IF(1,MATCH(A3,$A$11:$A$14,0))),3),)
G4	=INDEX(INDEX($A$11:$C$14,N(IF(1,MATCH(A4,$A$11:$A$14,0))),3),)
G5	=INDEX(INDEX($A$11:$C$14,N(IF(1,MATCH(A5,$A$11:$A$14,0))),3),)
G6	=INDEX(INDEX($A$11:$C$14,N(IF(1,MATCH(A6,$A$11:$A$14,0))),3),)
I3	=TRANSPOSE(INDEX(VLOOKUP(T(IF(1,TRANSPOSE(A3))),$A$11:$C$14,3,0),))
I4	=TRANSPOSE(INDEX(VLOOKUP(T(IF(1,TRANSPOSE(A4))),$A$11:$C$14,3,0),))
I5	=TRANSPOSE(INDEX(VLOOKUP(T(IF(1,TRANSPOSE(A5))),$A$11:$C$14,3,0),))
I6	=TRANSPOSE(INDEX(VLOOKUP(T(IF(1,TRANSPOSE(A6))),$A$11:$C$14,3,0),))
C3	{=INDEX($C$11:$C$14,MATCH(TRUE,$A$11:$A$14=A3,0))}
C4	{=INDEX($C$11:$C$14,MATCH(TRUE,$A$11:$A$14=A4,0))}
C5	{=INDEX($C$11:$C$14,MATCH(TRUE,$A$11:$A$14=A5,0))}
C6	{=INDEX($C$11:$C$14,MATCH(TRUE,$A$11:$A$14=A6,0))}
H3:H6	{=INDEX(INDEX($A$11:$C$14,N(IF(1,MMULT(N(TRANSPOSE($A$11:$A$14)=$A$3:$A$6),ROW($A$11:$A$14)-ROW($A$10)))),3),)}
Press CTRL+SHIFT+ENTER to enter array formulas.